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

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

=B115
etc...

HTH,
Bernie
MS Excel MVP


"Werner Rohrmoser" wrote in message
oups.com...
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