Help with Conditional Formatting - Tearing my hair out.

hellfire45

Active Member
Joined
Jun 7, 2014
Messages
454
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,498
Messages
5,741,505
Members
423,663
Latest member
kaveh87rsh

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
Top