Help with Conditional Formatting - Tearing my hair out.

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
462
I have a table with about a dozen columns on it and maybe 200 rows.

Each cell has 2 custom formulas for conditional formatting (As seen below). In the below formulas the N26 refers to the cell in the aforementioned table that the conditional formatting is going. And the Z26 and $Y26 are formulas off to the side, not on the table, that are calc'ing things that I want the conditional formatting formula referencing. However, when I format paint the conditional formatting, these below formulas do not behave as normal formulas would with the locking references. For example, column T in the table will ultimately end up with the CF formula =IF(AND(S26<0,AE26>0),TRUE,FALSE) when I expect =IF(AND(T26<0,AF26>0) This type of issue seems to occur not only on the column references but the row ones as well. So if I drag down to row 30 for instance, the formula might still display row 26. What the heck is happening here? It's killing me.

Conditional Formatting Examples:
=IF(AND(N26<0,Z26>0),TRUE,FALSE)
=IF(AND(N26>1,$Y26=1),TRUE,FALSE)
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Do you want to paste in a picture of your manage rules screen ?
Perhaps selecting just N26, and in the below Current Selection.
PS: You don't need the If statement the AND statement alone will return True or False

1622191488613.png
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,466
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top