Format Variable Number Of Excel Worksheets

Question

I used the Microsoft Excel 2016 macro recorder to come with the code below. It works great on a single worksheet. I get a workbook once a month with a different number of worksheets. I would like to automate this task by using Excel VBA. Is there an easy way to loop through each worksheet applying the following formats? The names of each of the tabs are always different.

...

   Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Passed", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Failed", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Error", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False


    Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Passed", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Failed", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Cells.Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="Error", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16776961
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    ActiveCell.FormulaR1C1 = "Passed"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Failed"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Error"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Total Defects"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "Manufacturing Days"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Yield"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Percent Defects"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A1:A3").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Total " & Chr(10) & "Defects"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "Manufacturing" & Chr(10) & " Days"
    Range("A7").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Columns("A:A").EntireColumn.AutoFit

...

Answer 1

Of course there is. You have to follow this simple steps:

1. Wrap your formatting code into a Sub, like this:

   Sub FormatSingleSheet()
       Cells.Select
       Selection.FormatConditions.Add Type:=xlTextString, String:="Passed", _
           TextOperator:=xlContains
       Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
   
       With Selection.FormatConditions(1).Font
           .Bold = True
           .Italic = False
           .Color = -16711681
           .TintAndShade = 0
       End With
   
       '[...] Rest of the code [...]
   End Sub
   

2. Create a Sub that calls the previous one for each of your Worksheet:

   Sub FormatAllSheets()
       Dim index As Integer, WS_Count As Integer
       WS_Count = ActiveWorkbook.Worksheets.Count
   
       For index = 1 To WS_Count
           ActiveWorkbook.Worksheets(index).Activate
           Call FormatSingleSheet
       Next
   End Sub
   

This will apply your formatting to all your Sheets.

Note: I've tested this code on my Workbook and it works.

Hope this helps.