Conditional Formatting - Relative References

[Carly]

I have got a spreadsheet where in D5 I want to have the following conditional formatting:

If D5 is blank then nothing
If D5 < C5 then Red
If D5 >= C5 then Green

I can get this to work on D5, but when I want to copy & paste the data down the cell from D5 to D100 then the absolute references kick in and always look at C5 instead of the relative row.

Can anyone advise what I am doing wrong?

 

[Akashwani]

Hi Carly,

Is this what you want?...

Excel Workbook
	C	D
5	15	25
6	70	55
7	45	35
8	60	40
9	55	45
10	50	30
11	45	50
12	40	60
13	75	65
14	30	70
15	95	75
Carly

Conditional Formatting formula for green is....

=$D5>=$C5

For red is....

=$D5<$C5

Make sure you highlight your range from D5 down.

I hope that helps.

Ak

 

[Carly]

Thank you, this works, but is extremely temperamental Have to go back in a number of times to change the references back to C5 & D5 as it jumps to way down the sheet.

Random.

Do you know what I would add in if I wanted it to be 'No Format' if D5 was blank?

 

[Akashwani]

Hi Carly,

Try this for red...

=AND($D5<>"",$D5<$C5)

Excel Workbook
	C	D
5	15	*
6	70	55
7	45	35
8	60	40
9	55	*
10	50	30
11	45	50
12	40	60
13	75	65
14	30	70
15	95	75
Carly

I hope that works for you.

Ak

 

[Carly]

Thank you very much, that helped loads