CODEWITHSUNDEEP
excelconditional-formatting
The Gootch
The Gootch

Reputation: 85

Conditional Formatting Not Applying To All Cells

I'm trying to highlight cells B3:D9 if the difference between D8 & D9 is more than or equal to 10,000.

I set up a true/false formula rule =ABS($D8-$D9) >=10000 that applies to cells =$B$3:$D$9. When it's true, the range of cells should be highlighted and when it's false, it should go back to normal.

The problem is that only cells B3:D4 and cells B7:D9 are affected - B5:D6 sees no change when the formula is true.

Another problem is that when the formula is false, the formatting doesn't go away.

Any help would be greatly appreciated.

enter image description here

Upvotes: 1

Views: 24723

Answers (2)

Guppy
Guppy

Reputation: 426

I struggled with this for an hour before realizing why my condition only applied to the first column. My reference range (French holiday dates) was on a separate sheet, and when selecting the range, I hit F4 as I usually do. BUT - you don't need to make the reference absolute, because it already is absolute...so when I hit F4 I made it relative...

Upvotes: 0

user4039065
user4039065

Reputation:

To apply conditional formatting to the whole block based on two cells you need to make the cell's row and references absolute, not just the columns.

Change the formula to:

=ABS($D$8-$D$9)>=10000

Upvotes: 2

Related Questions