Conditional formatting a dynamic range

Hi

In column A there are 3 possible integers in each row (-1, 0, 1). I am able to apply the conditional formatting that I want (grey fill for the cells) based on the value in column A with the following formula

=$A9>=0

Currently, my document has 240 rows, so the conditional formatting "applies to" =$B$9:$L$240 but when I add a row to the document I want the conditional formatting to apply to this new row. I tried the following formula in "applies to", but it would appear that the formula is translated to a static range once you click "apply"

=OFFSET(A9,0,0,COUNT(A:A),11)

Any ideas on how I can make the "applies to" dynamic?


Many thanks

Originally Posted by springboardjg

Any ideas on how I can make the "applies to" dynamic?

You can't, just set the range to something like =$B$9:$L$10000

Then change your condition to =SIGN($A9&"")>=0 so that the rule doesn't apply to empty cells.