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

[thoma011]

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]

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]

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

 

[thoma011]

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]

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

 

[Norie]

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

What ranges are you dealing with?

 

[thoma011]

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]

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]

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.

 

[Fluff]

Formula 1 & 2 can be joined together like
=AND($D4<=TODAY(), $K4="")