Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

conditional formating

0

Hi:

I am updating a training file, I need for the expiration date to be highlighted, I have set this for two years. 

My formula is =U13<TODAY()-730

This works great if I apply it to the cells with dates on them, the problem I have is that my file has a lot of empty cells and gaps (which will be filled out eventually) so I want to select the whole column for when the data is entered. But when I selected the whole column where the data goes, the empty cells also get a highlight.

How can I change this, please?

Thanks in advance 

excel
MS Office Version: 2003
posted by: chikivim on Fri Mar 27, 2020 at 12:28 am
Answer
Discuss

Answers

0

Please try this formula.

=AND(U13<>"",U13<TODAY()-730)

Have you considered making the font color white at the same time?

posted by: Variatus (rep: 4889) on Fri Mar 27, 2020 at 8:14 pm
Discuss

Discussion

Hi.
Thank you for your help, but that doesn't work either, the empty cells still colour red. I dont know what am I doing wrong,
chikivim Mar 29, '20 at 1:34 am
When you set the CF select the range you want to apply the format to. columns and rows in the formula must match the first cell of the selected range, meaning you cant apply my formula to a range U7:U50 without changing the two references to U13.
I have attached a workbook to my answer with the formula partially installed.
Variatus (rep: 4889) Mar 29, '20 at 6:07 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login