Help with Conditional Formatting - Tearing my hair out.

hellfire45

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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,694
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,579
Messages
5,770,958
Members
425,653
Latest member
UNSING

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