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

 

[thoma011]

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

 

[Fluff]

Remove all the CF formulas you have, then select D4:J27 then goto CF & use the formulae from post#3.

 

[Norie]

Instead of using Format Painter why not try adjusting the Applies to ranges when you goto Format>Conditional Formatting...?

 

[thoma011]

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!

 

[Fluff]

Glad it's sorted & thanks for the feedback

 

[thoma011]

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

 

[Fluff]

What formulae are you using?

 

[thoma011]

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!

 

[Fluff]

You will need the $ on K4, but not on D4, so it should be
AND(D4<=TODAY(),$K4="")

 

[thoma011]

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.