Camila has attended:
Excel Intermediate course
Excel Advanced course
Excel Forecasting and Data Analysis course
Excel Conditional formating
Hello there,
I am working on a file and I need to set conditional formating (colour) to range, based not only on that range values but also on values from other cells.
So if the value on a cell in the range >0, no problem, normal conditional formating rules apply to that cell.
If the value of a cell in the range =0, excel needs to look at another cell to determine whether to apply colour or leave that cell white.
for example:
if g4<80 the cell should be red
if g4>80 the cell should be green
if g4 = 0 excel should look at D4 and if that =0, cell should be while, if it doesn't, cell should be red red.
I am aware I could set this rule individually for each cell in the range, referencing it back to the D cell of that same row, however I am wondering if there is a quicker way to do this.
I hope my info is clear and I look forward to any assitance on this.
Many thanks,
Camila
RE: Excel Conditional formating
Hi Camila
This was a fun challenge to solve!
The important thing to remember about Conditional Formatting is
1) the formatting is only applied when the Condition = True
So any formula we create must produce a TRUE result for the formatting to be applied. No other result is considered.
2) Remember to change cell references between Absolute & Relative to ensure correct values are being looked at. Absolute is used by default so often needs to be changed to relative if applying conditional formatting to a selection.
So for your example you might need to create 3 conditional formats all using the 'Use formula' option
1) if g4 < 80 the cell should be red, this needs tweaking as later you testing for zero (0). So using the AND function allows to test for both conditions.
=AND(G4>0, G4<80)
formatting = RED
2) =G4>80
Formatting = GREEN
3)if g4 = 0 excel should look at D4 and if that =0, cell should be while, if it doesn't, cell should be red red.
=AND(G4=0, D4<>0)
Formatting = RED
Notice: D4 <> (Not equal to) 0
Testing for this as conditional formatting only changes on a true value we cannot have 2 checks. So if D4 = 0 it would produce FALSE and keep the colour white. If the value in D4 is anything else then it will return TRUE and change the colour to RED.
I hope this makes sense!
Kind regards
Dennis
