Page 1 of 3 123 LastLast
Results 1 to 10 of 21

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

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

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

    I've got a spreadsheet that I'm having trouble with turning the conditional formatting OFF once a "Completion Date" is entered into a target cell.

    In this sheet, staff enter an "Intake Date" in cell D4, which then allows other cells to populate due dates for report items. The Conditional Formatting I have set up highlights cells with certain colors based on proximity to the due-date for the report items, e.g. Cell is green when due date is is within 14 days of today, Yellow is within 7 days, Red is today or past due.

    My concern is that the cells will continue to show as Red, even though the items have been completed.

    What I'm looking for is a formula to make all of the Rules below turn off once data is entered into cell K4, allowing the other cells to revert to "No Fill".

    My current formulas in the Rules are:
    1. Formula: =D4=TODAY() Cell Fill is RED
    2. Formula: =D4 3. Formula: =(D4-TODAY())<8 Cell Fill is YELLOW
    4. Formula: =(D4-TODAY())<15 Cell Fill is GREEN


    Would this be something that I could enter as a rule before all the others with the "Stop If True" box checked?




    Any ideas?
    TIA

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

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

    You could try adding a condition to return FALSE if K4 is not empty, and TRUE if it is empty.

    Combining that condition with existing conditions using AND should ensure no formatting is applied if K4 has a value.

    1. Formula: =AND(D4=TODAY(), K4="") Cell Fill is RED
    2. Formula: =D4 3. Formula: =AND((D4-TODAY())<8, K4="") Cell Fill is YELLOW
    4. Formula: =AND((D4-TODAY())<15, K4="") Cell Fill is GREEN
    If posting code please use code tags.

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

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

    How about
    1 & 2. =AND(D4<=TODAY(),K4="")
    3 =and(D4-TODAY()<8,K4="")
    4 =AND(D4-TODAY()<15,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

  4. #4
    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.

    Thank you both!
    Ok, these seem to work great, but I noticed when i used Format Painter to apply the Conditional Formatting to the other cells in sheet, it becomes a hot mess, and only the original cell seems to work. Do I need to clear the other cells first, then paint again?

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,597
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

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

    How about adding a CF condition
    =(K4="") with no format set. Make it the first priority condition and check Stop If True.

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

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

    You need to change the cell references so that the columns/rows are absolute/relative as required.

    What ranges are you dealing with?
    If posting code please use code tags.

  7. #7
    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 Norie View Post
    You need to change the cell references so that the columns/rows are absolute/relative as required.

    What ranges are you dealing with?
    D4 through J27 is the largest range where I need the formatting to work. There are two other columns in which I have conditional formatting for due dates, but they're not as critical.

    This is what the formatting for D4 looks like now:
    https://ibb.co/TMZw4mY


    When I use Format Painter to drag to the rest of the row, this is what I get:
    https://ibb.co/znZfxwR

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

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

    Do you want the Cf to highlight D4:J4 based on the values in D4, or do you want each cell to highlight based on the value in that cell?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

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

    Try changing the formulas to this:

    1. Formula: =AND($D4=TODAY(), $K4="") Cell Fill is RED
    2. Formula: =D4
    3. Formula: =AND(($D4-TODAY())<8, $K4="") Cell Fill is YELLOW
    4. Formula: =AND(($D4-TODAY())<15, $K4="") Cell Fill is GREEN


    PS Not sure what's happening with the 2nd formula, it didn't appear in your original post either, but I hope you get the idea.
    If posting code please use code tags.

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

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

    Formula 1 & 2 can be joined together like
    =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

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
  •