Highlight Past Dates

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
Hi...I am trying to write a macro that highlights a cell that has past date. For example, if cell O12 has '11/15/2009' then I like that cell to be highlighted in light yellow (color index 6). I can do this using conditional formatting but I want this part of my macro. Below is what I have started.
[code\]

For i = lastRow To 2 Step -1
If Range("O" & i).Value = vbNullString And Range("O" & i).Value < 'today date 'Then
Range("O" & i).Interior.ColorIndex = 6
Else

[code/]

Thank you for your time.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Thank you Andrew. But, how do I write it?

Code:
If Range("O" & i).Value < today() Then
[code] 

I dont think this is going to work. Please advice.
 
Upvote 0
Thank you again Andrew...

I tried the following:

''*Yellow Highlight in Col P
For i = lastRow To 2 Step -1
If Range("P" & i).Value = vbNullString And Range("P" & i).Value < Date Then
Range("P" & i).Interior.ColorIndex = 6
''''''''''''''''''''''''''''''

But, after I run the macro, I can still see dates like '05/14/2009' and they are not highlighted. Should I remove 'AND' and replace with 'OR'?
 
Upvote 0
This is very strange...but this is still not working for me.

Col. P has dates and some blank cells. Very much, any date field where cell value is less then today date or if the cells are blank and then I want them highlighted. It is not working and driving me not so happy...although, I must thank you for your time and effort.
Should we try something else...other approach??
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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