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.

Hi

Set up a named range InsertNameName Myrange

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

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

Regards

Roger Govier


zahoulik wrote:
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.

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

"zahoulik" wrote in message
...

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.


--
zahoulik