Results 1 to 2 of 2

Thread: How is it possible for the same exact value in one cell to fail my conditional formatting while elsewhere it doesn't?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2015
    Posts
    113
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default How is it possible for the same exact value in one cell to fail my conditional formatting while elsewhere it doesn't?

    Here is what I'm trying to do.

    A meeting or documentation turn-in needs to be scheduled for a certain date & be held, otherwise the cell where the date goes turns red and bold yellow font. I use an internal calendar with key dates being referenced including today's date, the date of the beginning of the week (i.e. - 6-October), and Thursday's date (10-Oct). If the meeting is not scheduled at all, the cell is red. If the meeting is scheduled, but the date is prior to Thursday and it is Thursday or later, it turns red. This is the formula I'm using for conditional formatting:

    =AND(OR(NOT(YEAR(E1)=YEAR(NOW())),NOT(E1>Internals!$T$1),Internals!$Q$1>E1,AND(Internals!$Q$1>Internals!$U$1,E1< Internals!$U$1, Internals!O1=FALSE)
    This is only partially working. I'm having trouble thinking of how to write it. Basically, any scheduled date in the future should not turn red, while dates in the past should turn red only if they were scheduled prior to the last Thursday. Regardless, if the checkbox next to a cell is checked then it overrides the formatting & turns green again.

    My formatting is somehow turning one cell green while another is red (E3 & E9), even though they share the same date & formatting! The most aggravating part is my standalone IF formulas seem to be working fine using essentially the same formula.




    Last edited by gravanoc; Oct 7th, 2019 at 04:23 PM.

  2. #2
    Board Regular baitmaster's Avatar
    Join Date
    Mar 2009
    Location
    bristol, england
    Posts
    2,028
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How is it possible for the same exact value in one cell to fail my conditional formatting while elsewhere it doesn't?

    Couple of suggestions why this might happen, these are the first two things I'd be checking

    1) conditional formatting can get misaligned easily when working with multiple ranges. When you entered your formula above, the result will be different depending on whether you had E1 active (references self) or E2 active (references cell above), even if the selection is the same. Take extra care and double-check this

    2) floating point error, for example where a value 1.0000000000000001 looks very much like 1, but isn't for logic purposes. Copy and paste as values to see the actual value underneath
    Baitmaster G



    Always save your work before running code from me, I probably haven't tested it and I'm not responsible when you lose all your work :p
    I aim to improve understanding so you can develop your own solutions, not do all your work for you. Write a clear requirement and you're more likely to get help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •