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]

assuming the info is in one column (A) in the format you have shown above and each entry in a new cell perhaps something like

Dim cel As range

For Each cel In range("a1:A" & range("A" & Rows.count).End(xlUp).Row)
    If DateValue(Mid(cel, 1, 7)) < DateValue(Mid(cel, 10, 7)) Then
    Else
        cel.Font.Color = vbRed
    End If
Next cel

 

[Dazzawm]

assuming the info is in one column (A) in the format you have shown above and each entry in a new cell perhaps something like

Dim cel As range

For Each cel In range("a1:A" & range("A" & Rows.count).End(xlUp).Row)
    If DateValue(Mid(cel, 1, 7)) < DateValue(Mid(cel, 10, 7)) Then
    Else
        cel.Font.Color = vbRed
    End If
Next cel

Thanks but I got a runtime error '13' type mismatch on this line?

If DateValue(Mid(cel, 1, 7)) > DateValue(Mid(cel, 10, 7)) Then

 

[gordsky]

Thanks but I got a runtime error '13' type mismatch on this line?

If DateValue(Mid(cel, 1, 7)) > DateValue(Mid(cel, 10, 7)) Then

is your cel set out exactly like this "06/2016->12/2019"

 

[Dazzawm]

is your cel set out exactly like this "06/2016->12/2019"

Or 06/2016-> etc... with no end date.

 

[gordsky]

Or 06/2016-> etc... with no end date.

what are you expecting it to do if no end date
also did you "DIM cel as range" and have you used cel elsewhere in your code

 

[gordsky]

Or 06/2016-> etc... with no end date.

the type mismatch will be because of the missing second date. if you amend the code to below it works fine

Dim cel As Range

For Each cel In Range("a1:A" & Range("A" & Rows.Count).End(xlUp).Row)

    If Mid(cel, 10, 7) <> "" Then
    
         If DateValue(Mid(cel, 1, 7)) < DateValue(Mid(cel, 10, 7)) Then
           Else
              cel.Font.Color = vbRed
         End If
    
    End If

Next cel

End Sub

 

[Dazzawm]

Or 06/2016-> etc... with no end date.

what are you expecting it to do if no end date
also did you "DIM cel as range" and have you used cel elsewhere in your code

I am still getting the error on If DateValue(Mid(cel, 1, 7)) < DateValue(Mid(cel, 10, 7)) Then

"what are you expecting it to do if no end date" do nothing as it will not be an invalid date.

"also did you "DIM cel as range" and have you used cel elsewhere in your code" I have just run the code as you supplied.

 

[gordsky]

I am still getting the error on If DateValue(Mid(cel, 1, 7)) < DateValue(Mid(cel, 10, 7)) Then

"what are you expecting it to do if no end date" do nothing as it will not be an invalid date.

"also did you "DIM cel as range" and have you used cel elsewhere in your code" I have just run the code as you supplied.

Can you attach a proper sample sheet

 

[Dazzawm]

Can you attach a proper sample sheet

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