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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use the Date function to return today's date.

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

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
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.
 

sjha

Active Member
Joined
Feb 15, 2007
Messages
355

ADVERTISEMENT

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'?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it?

Rich (BB code):
If Range("P" & i).Value = vbNullString Or Range("P" & i).Value < Date Then
 

sjha

Active Member
Joined
Feb 15, 2007
Messages
355
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??
 

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,775
Members
416,982
Latest member
lisam77

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
Top