Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Question

I want to loop through each page and format the same ranges in each one. I have 10 sheets with the labels "1-10" correspondingly.

The code below runs, but I'm having trouble getting it to loop through each sheet.

Sub Macro1()
'

Dim wsList() As String, wsName As Variant, ws As Worksheet
   
wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")
    
For Each wsName In wsList
    Set ws = ThisWorkbook.Sheets(wsName)
      
    Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27").Select
    
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    
    Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    
    Range("O31:AF34").Select
    ActiveCell.FormulaR1C1 = "FALL"
    Range("O35").Select
    
Next wsName

End Sub

Answer 1

Range(...) instructs VBA to always use the ActiveSheet. You must instruct VBA to work on worksheet ws in order to achieve the desired result: ws.Range (...).

Moreover, Select and Selection are not necessary (and you should avoid using them because they are rarely necessary): Instead, either use a With-clause or assign the Range to a variable and use that:

For Each wsName In wsList
    Set ws = ThisWorkbook.Sheets(wsName)
      
    ' Option a) Use a Variable
    Dim myRange as Range
    Set myRange = ws.Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27")
    
    With myRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ' Or, without With
    myRange.Interior.Pattern = xlNone
    myRange.Interior.TintAndShade = 0
    myRange.Interior.PatternTintAndShade = 0

    ' Option b) Use With directly with intermediate variable
    With ws.Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29")
        .Locked = False
        .FormulaHidden = False
    End With

    ws.Range("O31:AF34").FormulaR1C1 = "FALL"
Next wsName