VBA highlight row w/ cell matching todays date

[frateg8r]

I'm always frustrated when I attempt to do what seems like a simple macro, and cannot accomplish it. Right now is one of those times.

After running a VBA based sort, I need my worksheet to highlight the rows whose cells in column Q match todays date (displayed as MM/DD).

Here's my code, which produces...nothing:

Dim cell As Range
lr = Cells(ActiveSheet.Rows.Count, 23).End(xlUp).Row
For Each cell In Range("Q2:Q" & lr)
        If cell.Value = Date Then
            cell.EntireRow.Interior.ColorIndex = 6
        Else
            cell.EntireRow.Interior.ColorIndex = xlNone
        End If
Next

Can someone kindly steer me in the right direction?

PS - conditional formatting is not the right solution for this application.

TIA

 

[Trevor G]

Try this

Sub col2A()
Range("Q2").Select
Do Until ActiveCell = ""
ActiveCell.Offset(1).Select
If ActiveCell = Date Then ActiveCell.EntireRow.Interior.ColorIndex = 6

Loop
End Sub

 

[MickG]

I think you have the wrong column!!!

Lr = Cells(ActiveSheet.Rows.Count, 17).End(xlUp).Row

 

[Rick Rothstein]

After running a VBA based sort, I need my worksheet to highlight the rows whose cells in column Q match todays date (displayed as MM/DD).

Here's my code, which produces...nothing:

Dim cell As Range
lr = Cells(ActiveSheet.Rows.Count, 23).End(xlUp).Row
For Each cell In Range("Q2:Q" & lr)
        If cell.Value = Date Then
            cell.EntireRow.Interior.ColorIndex = 6
        Else
            cell.EntireRow.Interior.ColorIndex = xlNone
        End If
Next

Can someone kindly steer me in the right direction?

Let's get an obvious question out of the way first... does Column W (what you are using to calculate the last row) contain data for at least as many rows as Column Q does? Also, if you are only checking Column Q for dates, why not use it to calculate the last row (or do you have other code coming later that may need the different last row the Column W provides)?

 

[RudiS]

Your code is actually fine, BUT if the date is not a valid date, (ie: a text date) is will not hilite.
Try this version

Dim cell As Range
Dim lr As Long
lr = Cells(ActiveSheet.Rows.Count, 23).End(xlUp).Row
For Each cell In Range("Q2:Q" & lr)
        If CDate(cell.Value) = Date Then
            cell.EntireRow.Interior.ColorIndex = 6
        Else
            cell.EntireRow.Interior.ColorIndex = xlNone
        End If
Next

 

[Rick Rothstein]

Your code is actually fine, BUT if the date is not a valid date, (ie: a text date)

The OP said "todays date (displayed as MM/DD)" which would imply to me it was a real date in which Cell Formatting was used to set the display in the cell, not a text one... or are you thinking the OP is using a formula like this in his cells =TEXT(NOW(),"MM/DD")?

 

[RudiS]

Nope; a moment of forgetfulness...
(I read it, but when I started thinking of a solution I slipped my mind.)

Well, there are a few replies to the OP's post, so lets see if there isn't a resolution within them...else more details will be needed.

Cheers Rick.

 

[frateg8r]

I think you have the wrong column!!!

Lr = Cells(ActiveSheet.Rows.Count, 17).End(xlUp).Row

Hey everyone - thank you so much for the feedback. YES, this was the problem. I had this assigned to column W in an earlier part of the script for a different reason, and the data in that column ends...the day before today! No wonder!

Remember what I said about frustration when simple macros don't work? It seems to always be the most simple, obvious oversights that cause it!

Thanks again!