Copy Conditional Formattting (Greater than)

[visionavenger]

Hello
I have created a rule to show if (eg) the value in Column B is greater than Column A, but when I use the Format Painter to copy the Conditional Formatting, Excel is using the absolute reference (even if I remove the $).
For example, IF B44 > A44, highlight Red.
When copied to B45, the formula is still checking row 44 and isn't updating to row 45.

Am I doing something wrong or using the wrong type of Conditional Formatting formula?

Thanks for any help or advice.

 

[Joe4]

Welcome to the Board!

Truth be told, I find the Format Painter a little flaky when it comes to Conditional Formatting.
I have much better luck doing the following:
- select the WHOLE range you want to apply the Conditional Formatting to, and then write the formula as it applies to the FIRST cell in that selected range

So maybe you select rows 44:100, and enter your CF formula like this:

=$B44>$A44

(note how columns are locked by rows are not).

 

[visionavenger]

Hi, Joe

Thanks so much for your reply. I tried your way, and it works (although it seems odd that it does).
You'd think that MS would fix the Conditional Formatting, but I guess it's way, way down a long list of fixes needed.

Anyway, thanks again for this. Much appreciated.

 

[Joe4]

You are welcome.

Thanks so much for your reply. I tried your way, and it works (although it seems odd that it does).

What exactly seems odd about it?

 

[visionavenger]

I guess because you're only entering 1 value (ie, 44) yet it applies to dozens or hundreds of row. I realize that the Absolute Reference ($) is only applying to the columns and not the rows, but it just seems odd that you can highlight a hundred row, and Excel knows that it needs to increase (or decrease) all the row numbers, despite explicitly entering "44" in the formula.

 

[Joe4]

I guess because you're only entering 1 value (ie, 44) yet it applies to dozens or hundreds of row. I realize that the Absolute Reference ($) is only applying to the columns and not the rows, but it just seems odd that you can highlight a hundred row, and Excel knows that it needs to increase (or decrease) all the row numbers, despite explicitly entering "44" in the formula.

It is similar to if you add a formula in one cell, and click auto-fill to copy down the entire column for all your rows of data (or if you manually copy a formula down/across a range of cells).
Through the use of "$", Excel knows what range references you want to "lock down" as the formula travels down, and which you want to allow to "float" (so row number increases by one for every row you move down).