Apply Conditional Formatting VBA to All workbook Sheets

Question

I am very new to this, I am making conditional formatting to highlight numbers above the average of all values in a row.

I need to apply this formatting to all rows in all sheets in the workbook.

From what I've collected this is what I've got:

 Sub AllSheets()
         Dim ws As Worksheet
         For Each ws In Worksheets
            Range("A1:S1").Copy
            For Each r In Selection.Rows
            r.PasteSpecial (xlPasteFormats)
            Next r
            Application.CutCopyMode = False
        Next ws
 End Sub

But when I run this, it only applies it to the active selected cells. How would I fix this?

Answer 1

You need to make sure the Range() being used is actually the range in the ws. Otherwise, it just runs on the Activesheet.

 Dim ws As Worksheet
 For Each ws In Worksheets
    ws.Range("A1:S1").Copy 
    For Each r In Selection.Rows
         r.PasteSpecial (xlPasteFormats)
    Next r
    Application.CutCopyMode = False
Next ws

End Sub

But, does this work? It's a little "tighter", mainly I'm trying to avoid using .Selection

 Dim ws As Worksheet
 For Each ws In Worksheets
    ws.Range("A1:S1").Copy ' Or replace this with actual range, not just `Selection`
    For Each r In ws.Range("A1:S1").Rows
         r.PasteSpecial (xlPasteFormats)
    Next r
Next ws

End Sub

Edit: just realized...Why even use the For each r loop, since you're only using one row anyways?

Answer 2

I need to apply this formatting to all rows in all sheets in the workbook.

Indeed, it takes practice to "know how to ask the right question". Once you can do that, a quick search:

Cycle through worksheets vba

  Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count

        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
        MsgBox ActiveWorkbook.Worksheets(I).Name

     Next I

  End Sub

Reference: https://support.microsoft.com/en-us/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook