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

thoma011

New Member
Joined
Mar 5, 2012
Messages
13
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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Remove all the CF formulas you have, then select D4:J27 then goto CF & use the formulae from post#3.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
Instead of using Format Painter why not try adjusting the Applies to ranges when you goto Format>Conditional Formatting...?
 

thoma011

New Member
Joined
Mar 5, 2012
Messages
13
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
Glad it's sorted & thanks for the feedback
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
What formulae are you using?
 

thoma011

New Member
Joined
Mar 5, 2012
Messages
13
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! :LOL:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,378
Office Version
365
Platform
Windows
You will need the $ on K4, but not on D4, so it should be
AND(D4<=TODAY(),$K4="")
 

Forum statistics

Threads
1,078,136
Messages
5,338,436
Members
399,234
Latest member
WaddoAU

Some videos you may like

This Week's Hot Topics

Top