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.