how to keep conditional formatting when adding row in middle of range
I have a list of data where i am formatting the rows based on the previous row to make it easier readable (basically if the data in the row above is the same it will dim the text in the row via a formula like $a3=$a2 for a range that starts in a3). However when people are entering or changing data they will want to enter rows in the list. When you do that the conditional formatting breaks the range where the conditional formatting applies and it basically skips the rows entered (i.e. if i enter a new row 17, now row 18 will check row 16 to do the conditional formatting). I tried using named ranges when setting the conditional formatting but this does not help since excel reverts to the cell references when applying.
Does anybody have any idea how to avoid this outside only allowing adding rows via a macro so that i can re-enter the cond formatting every time a row is added?
Thanks
Mischa
|