Comparing DateTime from cell with two dates VBA

Question

I am trying to compare the dates with time from inside a cell with Now()

I have the code so the style sheet autorefresh itself so when first date with time is overdue cell go green and when the second date and time is overdue the cell would go red.

I can only get this to work comparing the date without the time with DateValue.

There is a colunm with cells with two dates with times mayorly (sometimes there is only 1 date and sometimes there is only one date without time)

Cells with two dates with time would be as the example below.

-----------------
12/11/2011 09:00
13/11/2011 15:00
-----------------

This is what I have so far after several attempts (cosidering that many of the attemps have been deleted already)

Sub Worksheet_Change()

 Set aWorkBook = Workbooks("Workbook.xls").Sheets("sheet 2").Range("C3:C10")


    For Each Cell In aWorkBook
    'MsgBox (Mid(Cell.Value, 1, 19))
    If Cell.Value <> "" Then
    MsgBox (Now < Mid(Cell.Value, 11, 6))
    'MsgBox ((Mid(Cell.Value, 1, 17)) < Now())
    'MsgBox ((Cell.Value))

        If (CDate(Mid(Cell.Value, 1, 17)) < Now()) Then
              'MsgBox ("Hello")
              'Cell.Interior.ColorIndex = 3
          End If

       End If
    Next
End Sub

In this case I am using msgbox to test outcome but not success.

Any help would be very much appreciated.

Answer 1

Looking at your code it apears the date/time values are strings. If thats the case use

(DateValue(Cell) + TimeValue(Cell)) > Now()

If cells contain values formatted as dates, use

Cell > Now()

You might be better off using Conditional Formatting rather than the _Change event. Eg to format cell A3 use conditional formula (note, no $'s)

(DateValue(A3) + TimeValue(A3)) > Now()

then copy paste formats to any other cells you want