Conditional Formatting copy / update


New Member
Mar 16, 2011
Hi All,

Ive got an issue where i want to copy conditional formatting down through a sheet and have the rules upadate themselves incrementally....

Basically i have six boxes in a row with numeric values - then i have 2 cells hidden for each row with min and max values which the conditional formatting compares against.

The top row has cells F10 - K10 and the cells with min and max values are X10 and Y10.

There are three rules associated with F10 - K10 which are:

Values between X10 and Y10 = Green text
Values < X10 = Red text
Values > Y10 = Red text

This works well - however X10 an Y10 values are based on formulas else where on the sheet and there is an alternative version of these cells for each row.... so for row 11 the cells are F11 - K11 and X11 / Y11 and so on....

Obviously if this were a normal formula id highlight F10 - K10 and simply drag down until i had the required amount of rows (F59 - K59) and the forumula would auto update. I've tried this with cells containing conditional formatting- but the rule i have in place dont update.... they just carry over.... so the conditional formatting associated with cells F59 - K59 still compares back to cells X10 and Y10 and not X59 / Y59.

Is there any way to get around this without having to sit there and create three rules for each row?

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Gerald Higgins

Well-known Member
Mar 26, 2007
Do you have $ symbols in the formula in the conditional formating dialog box ?

For example, the bit that refers to X10, does it actually contain $X$10 ?

If yes, try editing out the second $ symbol, and also try reading up on absolute versus relative cell references.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics