Conditional Formatting Reference / Inserting Rows

[Werner Rohrmoser]

Hello,

Application: Excel XP SP2
OS: Win XP 5.1.2600 SP1

Data:
A1 10
A2 20
A3 30

Cond. Format in:
A11 =$a$1 > 5 => green interior color
A12 =$a$2 > 5 => green interior color
A13 =$a$3 > 5 => green interior color

When I insert rows between row 1 and 2 the references
in the cond. format formulas do not change.
When I use cond. formatting only in row 11 and I insert
rows between row 1 and 2, then the formula
of the cond. formatting adjusts to the appropriate reference.

What's the secret behind this behaviour?

Regards
Werner

 

[Bernie Deitrick]

Werner,

Conditional formatting formulas are actually stored as strings. If you want to have them update,
you'll need to use a helper column of formulas.
For example, in B11, use the formula

=A1

and copy down for two cells, then change your CF formulas to

=B11>5
etc...

HTH,
Bernie
MS Excel MVP