Excel VBA Macro Find and Format

I am trying to create a macro that can find a particular string in a header, then format the cells in that column. For an example, I have a header called "Purchase Date", "Cap Date", and "Exp Date". I want to be able to find the first instance of "Date" used, format them as text, then find the next occurance and format, etc.

I have created one that will find only the first instance, then not look for any further. Any idea? I have looked up "Find" and "After", but cannot get them to function correctly.

Thanks for any help.

It's not particularly elegant, but this will most likely do the trick for you. Just edit some of the hardcoded values like sheetname and make sure that you don't have more than 99 columns or 999 rows.

<!-- language: lang-vb -->

Public Sub FormatRowsInDateColumns()
Dim header As String

Worksheets("Sheet1").Activate

'loop through columns
For col = 1 To 99

    'check if header cell contains word date
    header = Cells(1, col).Value
    If InStr(1, header, "date", vbTextCompare) <> 0 Then
    
        'convert cell values to string
        For Row = 1 To 999
            'Formats value and perserves as text with an apostrophe
            Cells(Row, col).Value = "'" & Format(Cells(Row, col).Value, "yyyy/mm/dd")
        Next Row
      
        'set column format as text
        Columns(col).NumberFormat = "@"
    End If
Next col

End Sub