Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: How to turn conditional formatting OFF when a target cell has a date in it.

  1. #11
    New Member
    Join Date
    Mar 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    https://ibb.co/37xFfLR

    Here's a screen grab with comments. I've gotten the CF to work using the "AND" formulas above, but having problems with Format Painter.

    I've tried clearing all CF from the range, entering the new CF into D4, then Format Painter to the rest of the range, and there seems to be problems with the absolute references. Need help there
    Last edited by thoma011; Aug 15th, 2019 at 10:41 AM.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    Remove all the CF formulas you have, then select D4:J27 then goto CF & use the formulae from post#3.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,141
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    Instead of using Format Painter why not try adjusting the Applies to ranges when you goto Format>Conditional Formatting...?
    If posting code please use code tags.

  4. #14
    New Member
    Join Date
    Mar 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    Thanks to all! Got it to work today. Changed the original CF to include "$D4 and $K4" and all worked great. You all are awesome!

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    Glad it's sorted & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    New Member
    Join Date
    Mar 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    Ok, I seem to have spoken too soon. I've adjusted the "applies to" range to reflect "$d$4:$j$27" but for some reason, cell E4 isn't responding to the CF... Hmmm...

    https://ibb.co/2MrgB9R

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    What formulae are you using?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #18
    New Member
    Join Date
    Mar 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    I used the formulae from post 3, then applied range as noted above. All seemed to work, but when I entered some test dates, I noticed that column E and J didn't seem to be responding.
    I've also started to rethink how to approach this... Is there a way to tell the CF to look at cell K4 first, and if it contains any data at all (or a date in this case), then to stop applying the other rules? A previous poster suggested this, but I haven't given it a shot yet.

    I also tried the formulas that included the absolute references with $D4 and $K4, which also seemed to work, but it was changing the entire row's color based on the date in D4 rather than the individual cell. I apologize if I'm making this more confusing, but I'm like a dog with a bone now to make this work!

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,012
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    You will need the $ on K4, but not on D4, so it should be
    AND(D4<=TODAY(),$K4="")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #20
    New Member
    Join Date
    Mar 2012
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to turn conditional formatting OFF when a target cell has a date in it.

    Quote Originally Posted by Fluff View Post
    You will need the $ on K4, but not on D4, so it should be
    AND(D4<=TODAY(),$K4="")
    This worked like a charm! Again, thanks so much for the help!
    Sheet looks great and is working as planned.

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
  •