Using Excel 2016.
In the “Applies To” Section, I have =$F:$F because I want it to apply to column F
In the ‘rule’ the formula is =AND($F1=$F2,NOT(ISBLANK($F1)))
Intent: Highlight cells, in Column F, that have a value equal to the cell below it. This is intended to call out that it’s okay to delete this row if you want to, because the next row has the same value in column F. (And also doesn’t highlight blank cells.) (The selection of which rows to delete or not is manual and based on other criteria not relevant here.)
So after putting in the formula and the ‘applies to’ section it works exactly as intended.
But when I delete any row, the ‘applies to’ section fragments and everything breaks.
As an example, cells F187, F188, and F189 all have the same data. Cells F187 and F188 are highlighted correctly. I right click row 188 and delete it. Cell F187 loses its highlight even though the ‘new’ value in Cell F188 is the same. The entire point of the highlighting is now broken because it’s not correctly calling out that F187 is still a row that’s safe to delete. I go to check the conditional formatting rule and see the “applies to” is now =$F$1:$F$186,$F$188:$F$1048574
How do I prevent this from happening every time I delete a row? I want the “applies to” to be permanent and never change any time I perform an operation like deleting a row. I wish there was some kind of ‘lock’ function that I could ‘lock’ the ‘applies to’ and make it stop fragmenting.
Not sure if it’s relevant but some rows are merged across columns. For instance on row 179 the cells from columns A to H are merged.
I searched around and found something that said to try named ranges or using the indirect function, but that may work elsewhere, but it does NOT work in the ‘applies to’ in the conditional formatting rules manager, unfortunately.
Would prefer to avoid VBA script solution, but will consider one if it’s one that occurs automatically in the background and the sheet could otherwise be used as normal.
Thanks in advance for help!
