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:
- Tried using indirect - but excel seems to resolve the formula and saves the formatting rule and hence does not work with inserts as expected
- 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.
$O$19:$O$105
toO19:O105
. Then insert your row and see if it works like you expect. And a warm welcome to SO, btw! – Scott Holtzman