Conditional formatting - Problem with "Applies to"

Question

Hi,

I am currently having an issue with conditional formatting. I applied a rule to a cell like this one: =$Q3<$V3. The "applies to" window appears automatically as $Q$3 (that is correct, because this rule has to apply just to this cell). However if I drag the conditional formatting (with format painter) to more rows, I face two problems:

1. The rule does not change as I expect: it remains =$Q3<$V3, while in the next row it should be =$Q4<$V4 (considering that the dollar is just before the column);
2. The "applied to" window does not become $Q$4, but it becomes an interval of cells like $Q$3:$Q$300.

In my comprehension this means that the initial formula =$Q3<$V3 is now applied to all this interval $Q$3:$Q$300, but that is not what I need. I thought that putting the $ before the column was sufficient to make the formula change the row, but it is not working. I already tried to eliminate the $ or everything I could but I am not finding any solution.

Yesterday I was working on a similar file and it was ok at first try, I do not know why in this case it is not working.

Thank you very much for your help!

Have a nice day

BC

Answer 1

Thank you Hans!

Very useful!

Beatrice

Answer 2

The rule shown in the Edit Formatting Rule dialog always displays the formula for the first cell in the Applies To range.

So whether you select Q4 or Q12 or Q300, the dialog will display =$Q3<$V3. However, Excel should dynamically adjust the formula that it actually applies: in Q4 it will apply =$Q4<$V4 etc.