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

thoma011

New Member
Joined
Mar 5, 2012
Messages
13
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
Joined
Apr 28, 2004
Messages
75,277
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
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
How about
1 & 2. =AND(D4<=TODAY(),K4="")
3 =and(D4-TODAY()<8,K4="")
4 =AND(D4-TODAY()<15,K4="")
 

thoma011

New Member
Joined
Mar 5, 2012
Messages
13
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
Joined
Jan 15, 2007
Messages
22,683
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
Joined
Apr 28, 2004
Messages
75,277
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
Joined
Mar 5, 2012
Messages
13
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
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
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
Joined
Apr 28, 2004
Messages
75,277
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
Formula 1 & 2 can be joined together like
=AND($D4<=TODAY(), $K4="")
 

Forum statistics

Threads
1,077,641
Messages
5,335,411
Members
399,014
Latest member
emalabel

Some videos you may like

This Week's Hot Topics

Top