Conditional Formatting contradicting formula

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
149
Gee, my favorite topic! Conditional formatting is kicking my *** again.

Problem Statement: My conditional formatting formula is applying formatting to the correct & incorrect cells, all except cells with vanilla text in them in column E. What is disquieting about this situation is that I have the near-equivalent formula in a regular cell which is what I copied and pasted into the cond. formatting formula. The only difference between the two is that the normal cell formula contains an IF statement, which was castrated on the move to the cond. format formula, since IF statements don't seem to work there. Here's the two, side-by-side:

=IF(OR(NOT('Job Planning'!E3>$S$3),$P$3>'Job Planning'!E3,N3=FALSE, NOT(YEAR('Job Planning'!E3)=YEAR(NOW())), NOT('Job Planning'!E3>$T$3)),"RED","GREEN")
=OR(NOT(E1>Internals!$S$3),Internals!$P$3>E1,Internals!N3=FALSE, NOT(YEAR(E1)=YEAR(NOW())), NOT(E1>Internals!$T$3))

To clarify the conditions I've set, the first is checking whether the scheduled date in E is greater than date at the beginning of the week, currently 29-Sep. Second, is today's date greater than the date in E. Third, is the checkbox's linked cell set to FALSE? Fourth, it's checking to make sure the year is the current year. Finally, if the the scheduled date in E exceeds Thursday's date.

The truly bizarre part is that if I check boxes below a certain, it will unformat the cell two rows above it. I've tried adjusting the formula to E1, E3, etc. I figure some part of that is the problem.

Pictures:





 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
149
Update: It's working now. The way I fixed the earlier problem was by re-arranging the cells in the reference sheet to all be in the 1st row.
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,023
Office Version
2019
Platform
Windows
Gee, my favorite topic! Conditional formatting is kicking my *** again.
The only difference between the two is that the normal cell formula contains an IF statement, which was castrated on the move to the cond. format formula, since IF statements don't seem to work there.
It depends on the output of the formula, but more often than not they will fail, in an attempt to help you understand the logic behind cf a little better.

Conditional formatting is only applied where the formula returns a logical TRUE, this is regardless of the functions used.

If your formula results in TRUE or any number (including dates, decimals, fractions, times, currency) other than zero then the format will be applied.

If your formula returns FALSE, zero, an error, or any text (including numbers formatted as text) then the format wil not be applied.

When you apply conditional formatting to a range, it is advisable to select the range from top left to bottom right, then base the formula on the top left cell when considering relative ranges, etc. Doing it any other way can mess things up (the relative ranges are based on the position of the activecell within the applied range when you set the rule).

Finally, remember the 'stop if true' option if you have multiple rules for any given range, especially if conditions overlap so that 2 or more rules could result in true. In this scenario, the rule that should be applied last should be top of the list with the box ticked. Getting this part wrong is probably the most common cause of incorrect formatting being applied.

Hope this helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,680
Messages
5,445,931
Members
405,371
Latest member
Theglyde

This Week's Hot Topics

Top