Excel VBA - Date Cells automatically change back to Regional Date Format

Question

I need to do validation in the active sheet.

The columns - Q, AA, AI, AS, BH and BI should be in Date format mm/dd/yyyy.

If those are not in mm/dd/yyyy format; then the cells are to be colored in Red Background and those entries to be sent to "Observations" Sheet in the same Excel Workbook as hyperlinks.

(Apart from it I have few other requirements.)

For all those I have the following code.

Dim celArray, arr, Key1, KeyCell, celadr, celval, cell6 As Variant


    celArray = ("Q,AA,AI,AS,BI,BH")
    arr = Split(celArray, ",")
    For Key1 = LBound(arr) To UBound(arr)
    KeyCell = arr(Key1)
    Range(KeyCell & "2:" & KeyCell & "" & LastRow).Select

    ''Selection.Clearformats
'    Selection.TextToColumns Destination:=Range(KeyCell & "2"), DataType:=xlDelimited, _
'        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
'        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
'        :=Array(1, 3), TrailingMinusNumbers:=True
'    Columns(KeyCell & ":" & KeyCell).NumberFormat = "mm/dd/yyyy"


    For Each cell6 In Selection
        celadr = cell6.Address
        celval = cell6.Value '
        If Len(celval) > 1 Then

    Dim fistby As Integer
    Dim secby As Integer
    Dim tmpdte As Integer
    Dim tmpyr As Integer
    Dim tmpmth As Integer

   '       If KeyCell = "Q" Then
   '        Debug.Print celadr
   '       End If


        If IsDate(celval) Then
            If KeyCell <> "BI" And KeyCell <> "BH" Then
                If Range(celadr).Offset(0, 1).Value <> "" Or Range(celadr).Offset(0, 2).Value <> "" Or _
                Range(celadr).Offset(0, 3).Value <> "" Or Range(celadr).Offset(0, 4).Value <> "" Or _
                Range(celadr).Offset(0, 5).Value <> "" Or Range(celadr).Offset(0, 6).Value <> "" Or _
                Range(celadr).Offset(0, 7).Value <> "" Then
                     Range(celadr & ":" & Range(celadr).Offset(0, 7).Address).Interior.Color = vbRed
                     shname = ActiveSheet.Name
                     Sheets("Observations").Range("A65536").End(xlUp).Offset(1, 0).Value = celval
                     strstr = "'" & shname & "'!" & Range(celadr).Address(0, 0)
                     Sheets("Observations").Hyperlinks.Add Anchor:=Sheets("Observations").Range("A65536").End(xlUp), Address:="", SubAddress:= _
                     strstr
                End If
            End If
        End If

        fistby = InStr(celval, "/")
        secby = InStr(fistby + 1, celval, "/")

    If fistby <> 0 Then
        tmpdte = Mid(celval, fistby + 1, ((secby - 1) - fistby))
        tmpmth = Left(celval, fistby - 1)
        'tmpyr = Right(celval, 4)
    End If

    If KeyCell = "Q" Then
        If fistby = 0 Or tmpmth > 12 Or tmpdte > 31 Then
             Range(celadr).Interior.Color = vbRed
             shname = ActiveSheet.Name
             Sheets("Observations").Range("A65536").End(xlUp).Offset(1, 0).Value = celval
             strstr = "'" & shname & "'!" & Range(celadr).Address(0, 0)
             Sheets("Observations").Hyperlinks.Add Anchor:=Sheets("Observations").Range("A65536").End(xlUp), Address:="", SubAddress:= _
             strstr
        Else
        If (Len(celval) <> 7 + fistby Or Mid(celval, fistby, 1) <> "/" Or Mid(celval, secby, 1) <> "/") Or Range(celadr).Offset(0, 8).Value <> "" Then
             Range(celadr).Interior.Color = vbRed
             shname = ActiveSheet.Name
             Sheets("Observations").Range("A65536").End(xlUp).Offset(1, 0).Value = celval
             strstr = "'" & shname & "'!" & Range(celadr).Address(0, 0)
             Sheets("Observations").Hyperlinks.Add Anchor:=Sheets("Observations").Range("A65536").End(xlUp), Address:="", SubAddress:= _
             strstr
        End If
    End If
    Else
    If fistby = 0 Or tmpmth > 12 Or tmpdte > 31 Then
        Range(celadr).Interior.Color = vbRed
        shname = ActiveSheet.Name
        Sheets("Observations").Range("A65536").End(xlUp).Offset(1, 0).Value = celval
        strstr = "'" & shname & "'!" & Range(celadr).Address(0, 0)
        Sheets("Observations").Hyperlinks.Add Anchor:=Sheets("Observations").Range("A65536").End(xlUp), Address:="", SubAddress:= _
        strstr
    Else
    If (Len(celval) <> 7 + fistby Or Mid(celval, fistby, 1) <> "/" Or Mid(celval, secby, 1) <> "/") Then
        Range(celadr).Interior.Color = vbRed
        shname = ActiveSheet.Name
        Sheets("Observations").Range("A65536").End(xlUp).Offset(1, 0).Value = celval
        strstr = "'" & shname & "'!" & Range(celadr).Address(0, 0)

        Dim adrr As Variant
        adrr = Sheets("Observations").Range("A65536").End(xlUp).Address
        End If
        End If
        End If
        End If
    Next cell6
    'Columns(KeyCell & ":" & KeyCell).NumberFormat = "mm/dd/yyyy"
    Next Key1

The above codes work fine and colors cells whichever have entries such as dd-mm-yyyy OR dd/mm/yyyy OR mm-dd-yyyy in Red Background and sends those entries to "Observations" sheet as hyperlinks.

But problem is when I try to correct such erroneous entries to correct format - "mm/dd/yyyy" and re-run my vba code; I find that those cells are not corrected and are back in the original erroneous format.

i.e. Am not able to edit the erroneous cells, though I don't have any code to protect the cells from editing.

Can anyone tell where I am wrong - Or any improvements in the above code?

Answer 1

This happened due to 'Regional formatting problem'

I changed number format of dates from 'date' format category to 'text' and now am able to correct the erroneous date cells.