Frustration with date validation in Excel vba

[minette]

I am trying to do some date validation in Excel, and although it works on some dates, it does not work on others.

I have a sheet called “HH”. This sheet has lots of rows, but I'm only running the validation on the specified rows if column A is not empty and only starting at row 9. The data is in columns A to AE. I have a named range (which will change monthly), called "SLA_DATE". For this example the "SLA_DATE" is 27/06/2008.

When I run the VALIDATE code, it should check column L (which contains dates) and highlight the cell where it is less than the "SLA_DATE" (but if it's blank, or greater than or equal to “SLA_DATE”, it should not be highlighted).

I have noticed that some of the dates are highlighted, which should not be. So I've done a quick check by filling this range with 15 dates, starting at 24/06/2008, up to 08/07/2008. When I ran the code again, I noticed that all the dates less than the "SLA_DATE" is highlighted, which is correct. The dates from 27/06/2008 up to 30/06/2008 is not highlighted, which is also correct. But the dates from 01/07/2008 up to 08/07/2008 is highlighted, which is not correct.

For clarification, my test findings below:
24/06/2008 – highlighted - correct
25/06/2008 – highlighted - correct
26/06/2008 – highlighted - correct
27/06/2008 – not highlighted - correct
28/06/2008 – not highlighted - correct
29/06/2008 – not highlighted - correct
30/06/2008 – highlighted - incorrect
01/07/2008 – highlighted - incorrect
02/07/2008 – highlighted - incorrect
03/07/2008 – highlighted - incorrect
04/07/2008 – highlighted - incorrect
05/07/2008 – highlighted - incorrect
06/07/2008 – highlighted - incorrect
07/07/2008 – highlighted - incorrect
08/07/2008 – highlighted - incorrect

Please see my code below if anyone is interested at looking at. Seems weird to me, but it is obviously something I’ve done wrong in the code, I just cannot see what it is.

Sub VALIDATE()
    dtmDate = CDate(Date)
    dtmFirstDayOfMonth = DateSerial(Year(dtmDate), Month(dtmDate), 1)

    LastRow_HH = Sheets("HH").Range("A65536").End(xlUp).Row
    For n = 9 To LastRow_HH
        Call RUN_VALIDATION
    Next n
End Sub

Sub RUN_VALIDATION()
    vacurrentline = Range("A" & n, "AE" & n).Value
    If Range("A" & n).Value <> "" Then
        Range("L" & n).Select
        If CStr(vacurrentline(1, 12)) = "" Then
            Else
            If CStr(vacurrentline(1, 12)) < Range("SLA_DATE").Value Then
                Range("L" & n).Select
                Call FLAG_ERROR
            End If
        End If
    End If
End Sub

Sub FLAG_ERROR()
    With Selection.Interior
        .ColorIndex = 26
        .Pattern = xlSolid
    End With
End Sub

 

[RoryA]

Why do you use CStr(vacurrentline(1, 12))? You end up doing a string comparison and 01 will come before 26. Try a straight comparison, if you actually have dates in column 12.

 

[minette]

Rory, can't believe I've been so stupid. However, when I tried CDate, it gave me "type mismatch" error, but when I tried CVar, it works perfectly. Thanks for pointing me in the right direction.