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
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
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
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
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
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
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
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
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
