Conditional Formatting contradicting formula

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
138
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
138
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
7,979
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.
 

Forum statistics

Threads
1,089,299
Messages
5,407,452
Members
403,143
Latest member
CTremblay

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top