Excel_VBA highlight cells which has current date and different time

0
votes

Need help to highlight the cell which contains today's date and a different time. The code that I wrote is here, but it can only highlight the cells with the current date.

Sub Datechoose()

Dim rCell As Range
With Worksheets("owssvr")

For Each rCell In .Range("A2:M20")
    If rCell.Value = Date Then
        rCell.Interior.Color = vbGreen
    End If
Next
End With

End Sub

enter image description here

My aim is to highlight the cells with the current date(6/22/2018 and time). Please help me out...

2
vbaexcel
Today's date is 43273. Today at 12 noon would be 43273.5 and 6pm would be 43273.75. In other words Excel stores dates as number of days since 1/1/1900 and time as decimal fractions of 24 (hours). The current date + time is given by NOW() – Harassed Dad
Hi thanks for the response. But I my time will be different, only the date will be the current date. So how should I find it out? – DIPIN VARKEY
If ABS(rCell.Value-date()) < 1 then will find all cells with today's date irrespective of time – Harassed Dad

2 Answers

0
votes

Highlight your cells and give them a conditional format.

With A1 selected the condition below will return TRUE if the date portion of the date/time value is equal to todays date. 

=INT(A1)=TODAY()

As @HarassedDad said - 12 noon will be 43273.5 for 22nd June 2018.
INT(43273.5) cuts off the time leaving just the date to compare against your date value.

0
votes

Wrap the 2 comparison values in a format to ensure Excel reads them in the same manner.

if isdate(rCell) then
    If format(rCell.Value, "mm/dd/yyyy") = format(Date, "mm/dd/yyyy") Then
        rCell.Interior.Color = vbGreen
    End If
end if

Edit 1

you'll get an overflow error if the cell value is a numeric value outside of valid date parameters. You can avoid this by using the 'isDate' function to verify the cell is a date and can handle the format.