To validate date format in Excel

[lucpian]

I am trying to write a validation code to check the date format of cells within three different columns in Excel to ensure that the format matches "dd/mm/yyyy, and if not should give a message. I am new to VBA, and needs help with sample on how to approach it.

Thanks

Lucpian

[Bob Phillips]

See if this works for you

[vba]

Dim LastRow As Long
Dim cell As Range
Dim col As Range
Dim msg As String
Dim i As Long

For Each col In Range("A:C").Columns

LastRow = Cells(Rows.Count, col.Column).End(xlUp).Row
For Each cell In Cells(1, col.Column).Resize(LastRow)

If cell.NumberFormat <> "dd/mm/yyy" Then

msg = msg & cell.Address(False, False) & " - " & cell.NumberFormat & vbNewLine
End If
Next cell
Next col

MsgBox msg
[/vba]

[lucpian]

See if this works for you

[vba]

Dim LastRow As Long
Dim cell As Range
Dim col As Range
Dim msg As String
Dim i As Long

For Each col In Range("A:C").Columns

LastRow = Cells(Rows.Count, col.Column).End(xlUp).Row
For Each cell In Cells(1, col.Column).Resize(LastRow)

If cell.NumberFormat <> "dd/mm/yyy" Then

msg = msg & cell.Address(False, False) & " - " & cell.NumberFormat & vbNewLine
End If
Next cell
Next col

MsgBox msg
[/vba]

[lucpian]

See if this works for you

[vba]

Dim LastRow As Long
Dim cell As Range
Dim col As Range
Dim msg As String
Dim i As Long

For Each col In Range("A:C").Columns

LastRow = Cells(Rows.Count, col.Column).End(xlUp).Row
For Each cell In Cells(1, col.Column).Resize(LastRow)

If cell.NumberFormat <> "dd/mm/yyy" Then

msg = msg & cell.Address(False, False) & " - " & cell.NumberFormat & vbNewLine
End If
Next cell
Next col

MsgBox msg
[/vba]

It works when I test one condition, but when I test two formatted conditions it gives an error. Below is the code I wrote with the sample you provided.

Sub ValidationEffDateButton()
Dim strVal As String
Dim rRng As Range
Dim Cols As Integer
'Dim Rows As Integer
Dim C As Integer
Dim R As Integer
Dim dDate As String
Dim LastRow As Long
Dim cell As Range
Dim col As Range
Dim msg As String
Dim i As Long
'Set rRng = Range(B).Select
'Selection.NumberFormat = "dd/mm/yyyy"
For Each col In Range("B").Columns

LastRow = Cells(Rows.Count, col.Column).End(xlUp).Row
For Each cell In Cells(2, col.Column).Resize(LastRow)

If ((cell.NumberFormat <> "mm/dd/yy") Or (cell.NumberFormat <> "mm/dd/yyyy")) Then
'Sheet1.Cells("B").Interior.ColorIndex = 0
' MsgBox "Incorrect format"
msg = msg & cell.Address(False, False) & " - " & cell.NumberFormat & vbNewLine
End If
Next cell
Next col

MsgBox msg


'Next
'If (Len(strVal) <> 13) Then

' MsgBox "Incorrect value length"
'Else
' MsgBox "Correct value length"

'End If

'Next
End Sub
Please, what is wrong with this code