How to do conditional formatting using self reference in excel

Question

I want to apply conditional formatting (fill cell with red) to all rows in columns F, I, L. E,F,I,H,L,K columns contain number values.

Condition is if F3 < E3 fill F3 with red, I3 < H3 fill I3 with red, L3 < K3 fill L3 with red and so on. It will go on like: F4 < E4 true, I4 < H4 true, L4 < K4 true.

sample table:

http://pbrd.co/1bs0B2Z

How will I do this? I think I have to do self reference in conditionally formatted cells.

@Cool Blue Could you show me how to do it? What should the formula look like? — alwbtc, Dec 09 '13 at 14:30

score 6 · Accepted Answer · answered Dec 09 '13 at 16:07

6

in conditional formatting for F3, put =(F3<E3) - note you will have to type this, as clicking on the cells will give fixed addresses (with the $ sign), which we don't want.

Once you have this, copy the formatting to all required cells. Excel will adjust the range for each cell. This will not show up in the formula for the conditional formatting, but the format changes will show that it is being calculated dynamically.

answered Dec 09 '13 at 16:07

SeanC

15,695
5
45
66

I think you should have mentioned "active cell" in your answer, which I finally found out today. I have to use active cell's address in the formula, so that excel adjusts the formula on other cells – alwbtc Dec 10 '13 at 08:36
Whoa, this worked! I'm surprised Excel doesn't make this more intuitive. It's SUCH a cool way to use Conditional Formatting. Thanks! – Ryan Nov 10 '15 at 01:20

How to do conditional formatting using self reference in excel

1 Answers1