Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Conditional formatting

Resolved · Low Priority · Version 2007

Reply Mon 7 Nov 2011, 17:27Delegate Lisa said...

Lisa has attended:
Excel Advanced course

Conditional formatting

Hi there

Is there a way to change the colour of a specified range of cells on a row based on the values entered into one of the cells in that row?

I would like to be able to change the colour of the row when I enter any value greater than £1.

Thanks

Lisa

For upcoming training course dates see: Pricing & availability

Reply Wed 9 Nov 2011, 13:48Trainer Paul said...

RE: conditional formatting

Hi Lisa,

You can do this. Select your range, create a new rule and select the last option "use a formula...". In the formula bar click in the first cell of that range and then enter >=1 and format red.

Hope that worked for you.

Thanks
Paul

Reply Wed 9 Nov 2011, 14:05Delegate Lisa said...

RE: conditional formatting

Hi Paul

It kind of works! How do I get it to just change the row to a different colour. At the moment it's changing the entire range but really I just want to it change the colour of the row that I enter the values in to.

I tried just setting the range to one particular row but I'd like to avoid having to set individual rules for each row as the spreadsheet is massive.

thanks

Lisa

Reply Wed 9 Nov 2011, 14:33Trainer Paul said...

RE: conditional formatting

try doing the same thing for the first row but remove the first $ sign so the absolute reference is only on rows not the columns, then auto fill accros the page.

=G$5>=1

Reply Wed 9 Nov 2011, 15:19Delegate Lisa said...

RE: conditional formatting

Hi Paul
Removing the first $ sign just makes all columns change colour as soon as I click ok except for the one specified in the formula (G).
Thanks

Lisa

Reply Wed 9 Nov 2011, 16:32Trainer Paul said...

RE: conditional formatting

Hi,

I see what you mean. Just to clarify, you want the entire row to turn a colour when one cell within that row has 1 or more entered into it, and you want this to work for every row individually?

Firstly is that correct? If so can you tell me why you would want to do that so i can get a better understanding what your trying to achieve?

thanks
Paul

Reply Wed 9 Nov 2011, 17:08Delegate Lisa said...

RE: conditional formatting

yes that's correct.

The spreadsheet contains a list of sales opportunities. On each row you have company name, contact, address, telephone etc... Each time a sale comes in against one of these opportunities we enter the value of the sale and would like that row to change colour so we know the opportunity has been closed.

Hope that makes sense?

Lisa

Reply Wed 9 Nov 2011, 20:05Trainer Paul said...

RE: conditional formatting

Apart from putting a new rule on for each cell in the row using my first suggestion and then auto filling it to the columns, I don't know. I will however consult with one of our VBA programmers and see if they know how a quicker way. Please allow a day or so for me to come back to you on this.

thanks
Paul

Reply Tue 7 Feb 2012, 13:44Trainer Rodney said...

RE: conditional formatting

Hello Lisa,

Just checking to see if the conditional formatting is still not performing the way you want it to.

I don't have your data in front of me, however, I presume the sales data is entered into a single column. If this is so, then do the following:

1) Select the data range i.e. A2:G250 (don't include your headings) OR select the row numbers from 2 to 250 (if you want the ENTIRE row to be highlighted).

2) Click Conditional Formatting

3) Click New Rule

4) Select the Rule Type: Use a Formula to determine which cells to format

5) Enter the following formula: =$F2>1 (assuming that the sales data is entered into column F starting from row 2)

6) Format using whatever colours etc. you desire.

7) Click OK until you have exited the conditional formatting dialogue box.

8) Enter data into column F and the row will now be highlighted.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Mon 13 Feb 2012: Automatically marked as resolved.

Excel articles

· How To Pick The Best Chart Type In Excel
· Some Simple Yet Less Common Excel Functions
· Training Budgets In A Bad Economy
· How To Use The New Excel Web App
· How To Use The Cell Styles Functionality In Excel 2010

Please browse our web site to find out more about
excel consultants and other Microsoft training courses.

Excel tip:

Naming and Using Constants

Constants make calculations easier so worksheets are more easily understood. Constant values also need to be given relevant and memorably names. It is also easier to change the value of a constant.

For example:
Instead of entering 17.5% in each cell when you generate a VAT amount you could name a Constant "VAT" and assigning a "0.175" value to it. To do this:

From the 'Insert' menu select 'Name', then select 'Define'.

Enter the constant

View all Excel hints and tips