Hello everyone!

First, I'm a french speaker - sorry for my poor english.

I'm working in a marketing company and we have one mandate with a client to follow specific events that their customers do each month. We have two sources for this: one from another team, where they put what they have in a shared Excel, and another from our website, where the customers put the information themselves. At the end of every month, my team compare the information with our website database to see if there's any differences in the inputs of both sources. We developed a Query that compares easily both sources to get it done fast. In the shared Excel, the dates of each event are put in column H: that's where we have a problem.

See, in the website database, all dates are in this one format: yyyy-mm-dd. The other team, though, puts different formats for each event every month. It can be dd-mm-yyyy, mm/dd/yyyy, yyyy-dd-mm, etc.

With that, our query cannot make the comparison perfectly. We have to compare them manually, which is long work, and sometimes we miss errors before sending the report to our client.

Now, we tried to teach them to only use yyyy-mm-dd as a format, but it doesn't stick. So, I'm looking for a solution where the column could only accept one date format (yyyy-mm-dd), and if they try to put anything else, there's a message error.

Just to say: I tried data validation, but since they copy/paste their information, it doesn't work. I tried a VBA that forces the data validation to works even if it's copy/paste, but to no avail. Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngInterest As Range, rngCell As Range
    Dim boolUndo As Boolean
    Dim vDVType As Variant
    Const c_dvrange = "$H$2:$H$300"
    On Error Resume Next
    Application.EnableEvents = False
    Set rngInterest = Intersect(Target, Range(c_dvrange))
    If Not rngInterest Is Nothing Then
        vDVType = rngInterest(1).Validation.Type
        If Not IsEmpty(vDVType) Then
            For Each rngCell In rngInterest
                If rngCell.Value <> "" Then
                    If Not rngCell.Validation.Value Then
                        boolUndo = True
                        Exit For
                    End If
                End If
            Next rngCell
        Else
            boolUndo = True
        End If
        If boolUndo Then
            MsgBox "Action Not Allowed - Reversed", vbCritical, "Computer Says No"
            Application.Undo
        End If
    End If
    Application.EnableEvents = True
End Sub
I would prefer a VBA that do the data validation itself: only accepts one date format, no matter if it's a copy/paste, insert, etc., but didn't find any solution online. If someone can help, please do!


Thank you!