Conditional Formatting when inserting a row

[zahoulik]

My conditional formatting is =SUM($E$2:$E$9)<>$E$1. When this returns
true, all of the cells E2:E9 turn red. But in the future, I will need
to insert a row that needs to be included in the conditional
formatting. Assume that the conditional formatting returns false.
When I do insert a row, all of the cells turn red because the last row
is pushed down to E10 and therefore is not included in the original
conditional formatting function. Also, if I were to add in data at
E10, is there a way to automatically update the conditional formatting
function to include this cell?

Help is appreciated.

 

[Roger Govier]

Hi

Set up a named range Insert>Name>Name Myrange

Refers to =INDEX($E:$E,2,0):INDEX($E:$E,MATCH(9.99999999999999E+307,$E:$E))

Change your conditional formatting formula to
=SUM(Myrange)<>$E$1

Regards

Roger Govier

 

[Barb Reinhardt]

Have you tried inserting your row anywhere between row 3 and 8?