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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
How about
1 & 2. =AND(D4<=TODAY(),K4="")
3 =and(D4-TODAY()<8,K4="")
4 =AND(D4-TODAY()<15,K4="")
 
Upvote 0
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?
 
Upvote 0
How about adding a CF condition
=(K4="") with no format set. Make it the first priority condition and check Stop If True.
 
Upvote 0
You need to change the cell references so that the columns/rows are absolute/relative as required.

What ranges are you dealing with?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.:)
 
Upvote 0
Formula 1 & 2 can be joined together like
=AND($D4<=TODAY(), $K4="")
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top