Hello cardinalsfan0510,

Welcome to the Forum!

This macro will open all ".xls" files in the specified folder and check each worksheet's first row for Zip and Zip+4 in the cells values. If there are not at least two rows on the worksheet, the macro will skip the worksheet. You can add this macro to your existing code by copying the code and pasting it into a separate VBA module. You will then need to call this macro from the existing code when needed.

You will need to change the file path to where your files are located. Currently, the macro searchs for ".xls" workbook files. You can change the extension if needed. Change the variable Ext in the code to the extension you need. Remember to include the period before the extension.

Sub FormatZipColumns()

    Dim Cell As Range
    Dim Ext As String
    Dim Filename As String
    Dim Filepath As String
    Dim Rng As Range
    Dim Wkb As Workbook
    Dim Wks As Worksheet
    
        Filepath = "C:\Documents and Settings\My Documents"
        Ext = ".xls"
        
            Filepath = IIf(Right(Filepath, 1) <> "\", Filepath & "\", Filepath)
            Filename = Dir(Filepath & "*" & Ext)
            
            Do While Filename <> ""
               Set Wkb = Workbooks.Open(Filepath & Filename)
                   For Each Wks In Wkb.Worksheets
                       Set Rng = Wks.Range("A1", Wks.Cells(1, Wks.UsedRange.Column))
                       If Wks.UsedRange.Rows.Count > 1 Then
                           For Each Cell In Rng
                               If LCase(Cell) Like "*zip+4*" Then
                                  Cell.EntireColumn.Cells.NumberFormat = "0000"
                               Else
                                  If LCase(Cell) Like "*zip*" Then
                                     Cell.EntireColumn.Cells.NumberFormat = "00000"
                                  End If
                               End If
                           Next Cell
                       End If
                   Next Wks
               Wkb.Close SaveChanges:=True
            Loop
            
End Sub