Excel - Conditional Formatting - insert row

Question

Using Offset or Indirect in 'Applies To' does not seem to work. Is there any other way to stop conditional formatting from breaking after inserting row/s

I have a conditional format for a range for e.g. $O$19:$O$105. The condition is 'if cell value is > 10', it is formatted with red color.

The problem is - when I insert a row in excel, this formatting range splits and I get 2 formatting rules. For e.g. the 2 rules with range as $O$19,$O$21:$O$105 & $O$20 respectively, if I insert a new row at 20th row.

Typically for condition like the one above, it may not matter, if the rules are split into multiple ranges. But for conditions like 'highlight top 10', it causes undesired results.

I tried the following without much luck:

1. Tried using indirect - but excel seems to resolve the formula and saves the formatting rule and hence does not work with inserts as expected
2. Tried using offset - here again excel resolves the range same as above.

Anyone knows how to write a conditional format that does not break with row inserts?

[EDIT] I realized that insert row is not causing the splitting of the conditional formatting rules. I also copy a row and paste in the inserted row which is doing this. If I opt for special paste and choose formulas only, its working fine.

Answer 1

I know this is an old thread but here's another solution that's super simple and works great.

Simply insert a new row or column as desired. Then select and copy a row/column that has the correct conditional formatting. Past Special into the new row/column that you just created and select the option for "All merging conditional formats". Your conditional formatting rules should now be automatically updated.

Happy Excel-ing =)