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

thoma011

New Member
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<TODAY() Cell Fill is RED
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
 

Norie

Well-known Member
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
 

Fluff

MrExcel MVP, Moderator
How about
1 & 2. =AND(D4<=TODAY(),K4="")
3 =and(D4-TODAY()<8,K4="")
4 =AND(D4-TODAY()<15,K4="")
 

thoma011

New Member
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?
 

mikerickson

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

Norie

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

What ranges are you dealing with?
 

thoma011

New Member
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
 

Fluff

MrExcel MVP, Moderator
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?
 

Norie

Well-known Member
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.:)
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top