Macro To Highlight Invalid Dates

[Dazzawm]

I have files with a column with dates in like the format below. Some are invalid where the end date is before the start date (highlighted)! I need a macro please so I can put in my Personal Macro Workbook for use on different files, that will highlight these errors when the code is run. In this case we can use column A but I can amend code for the column to suit. Thanks.

06/2016->12/2019

06/2016->12/2019

02/2015->04/2017

02/2015->04/2017

02/2015->04/2017

02/2018->04/2017

02/2015->04/2017

02/2015->04/2017

02/2019->04/2017

02/2015->04/2017

02/2015->

02/2015->

05/2016->

 

[gordsky]

The code works on your first post with a sample sheet I knocked up. Have you stepped through the code to see if it fails on first cell or somewhere else?? The only other reason I can think is if you are using a table rather than cells. unless you are able to provide a sample sheet to test why it works on one but not the other I won’t be able to assist you further I am afraid.

 

[Dazzawm]

The code works on your first post with a sample sheet I knocked up. Have you stepped through the code to see if it fails on first cell or somewhere else?? The only other reason I can think is if you are using a table rather than cells. unless you are able to provide a sample sheet to test why it works on one but not the other I won’t be able to assist you further I am afraid.

Only difference really is the file I use it on is upwards of 100,000 rows.

 

[gordsky]

Only difference really is the file I use it on is upwards of 100,000 rows.

Have you stepped through the code to see if it fails on first cel or further down

 

[Dazzawm]

Have you stepped through the code to see if it fails on first cel or further down

I will do now

 

[Dazzawm]

Have you stepped through the code to see if it fails on first cel or further down

I think it may be because it needs to start in row 2. Row 1 has a header and not in a date format like the others.

 

[gordsky]

The layout and format of the dates are no different from the example in first post.

There was no mention in your original request of headers, none in your example and when I asked about a sample sheet you said it was as per the attachment.

change the range("A1:A") to reference in the code to reflect a2 then.

 

[mikerickson]

You might be better served with a UDF to test if its a valid string which you could use to trigger Conditional Formatting or something

Function IsValidOrder(aString As String, Optional Delimiter As String = "->", Optional Comparitor As String = "<") As Boolean
    Dim firstDate As Double
    Dim secondDate As Double
    
    On Error Resume Next
    firstDate = CDate(Split(aString, Delimiter)(0))
    secondDate = CDate(Split(aString & Delimiter, Delimiter)(1))
    If Err Then Exit Function
    On Error GoTo 0
    Select Case Comparitor
        Case "<": IsValidOrder = (firstDate < secondDate)
        Case "=<": IsValidOrder = (firstDate <= secondDate)
        Case "=": IsValidOrder = (firstDate = secondDate)
        Case ">=": IsValidOrder = (firstDate >= secondDate)
        Case ">": IsValidOrder = (firstDate > secondDate)
    End Select
    
End Function

I got a little happy addiing optional delimiters and which order to test.