Loop throgh sheets and check the date format

[kaushik03]

Hi all,

What I am trying to do here is:

I have a date stored at A1 in "MM/D/YYYY" format (e.g., 10/1/2013 = October,1, 2013). Date format at A1 will always be fixed.

Now the same date is stored in many places across the worksheets of this workbook. I want to run a macro which should check all the cells(of this workbook) wherever there is a date and check if the format is correct(i.e. "MM/D/YYYY"). If it finds a cell (or cells) does not match the required date format, it should return me the cell address as well.

I tried but not able to write the code properly. Could you please help me on this.

Regards,
Kaushik

 

[NARAYANK991]

Hi Kaushik ,

Can you try this ?

Public Sub Check_Date_Formats()
           Dim ref_fmt As String
           Dim wks As Worksheet
           
           ref_fmt = Range(ThisWorkbook.Names("Reference_Cell").RefersTo).NumberFormat
           
           For Each wks In Worksheets
               For Each cell In wks.UsedRange
                   If TypeName(cell.Value) = "Date" Then
                      If cell.NumberFormat <> ref_fmt Then
                         MsgBox cell.Address
                      End If
                   End If
               Next
           Next
End Sub

Reference_Cell is a named range referring to A1 , which contains the reference date format.

Narayan