How to do conditional formatting using self reference in excel

excel conditional-formatting self-reference
alwbtc picture alwbtc · Dec 9, 2013 · Viewed 8.6k times · Source

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.

Answer

SeanC picture SeanC · Dec 9, 2013

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.

Related questions

Conditional Formatting (IF not empty)

How do I conditionally format a cell so if not blank it is grey? I tried to do 'not equal', but it didn't work. I am using Windows Office 2003 with Windows XP at work. I don't see the same feature …

Read More
Change the color of cells in one column when they don't match cells in another column

I want to check if the values in one column are the same as values in another column. Whenever the values are not the same, I would like to change the color of these cells. For example: Column I, Column …

Read More
Conditional Formatting using Excel VBA code

I have Range object called DistinationRange which contains reference to range B3:H63 I want to apply the following two conditional formatting rules using Excel VBA code dynamically. (Because the range would not be same all the time) If Cell …

Read More