Scanning columns that have dates in them and change format

[Brawnystaff]

Below is an example of a macro that I use to convert dates in a spreadsheet from ddmmmmyyyy format to mm/dd/yyyy. In this case, I know which columns have dates in them (starting in row 2 on down, as row 1 as the column header). However, I routinely receive other spreadsheets with dates in columns that I do not know of in advance. Is there a macro that can "scan" for any date values in row 2, and then format the entire column where these dates are found to mm/dd/yyyy? Thanks.

Sub SelectColumns()
Range("A:B,O:Q,BJ:BK").Select
Selection.NumberFormat = "mm/dd/yyyy"
End Sub

[humdingaling]

are the cells actually in a date format? or are they text fields that look like a date?

[Brawnystaff]

They are in a date format..

[protonLeah]

Option Explicit
Sub DateTest()
    Dim TestRow As Range, _
        TestCell As Range
    Set TestRow = Cells(2, Columns.Count).End(xlToLeft)
    For Each TestCell In TestRow
        If IsDate(TestCell.Value) Then
            TestCell.EntireColumn.NumberFormat = "mm/dd/yyyy"
        End If
    Next TestCell
End Sub

[Brawnystaff]

I tried the macro, and it did not work. However, when I copy the columns with dates to a new spreadsheet and run the macro, it does work. Strange.

Dates are formatted "Custom" as [$-409]ddmmmyyyy. I can also right-click on them and convert to mm/dd/yyyy. Spreadsheet is downloaded from SAS.. Any ideas?

Attached is a sample of the spreadsheet with the dates...

[humdingaling]

changes proton's code slightly as it was only picking the last column

Option Explicit
Sub DateTest()
    Dim TestRow As Range, _
        TestCell As Range
    Set TestRow = Range(Cells(2, 1), Cells(2, Columns.Count).End(xlToLeft))
    For Each TestCell In TestRow
        If IsDate(TestCell.Value) Then
            TestCell.EntireColumn.NumberFormat = "mm/dd/yyyy"
        End If
    Next TestCell
End Sub

[Brawnystaff]

Thanks humdingaling. That worked.. Will mark as SOLVED.