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.  




    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

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.