For Each worksheet - select a column

[JohnKauffman]

I can't seem to get syntax right to select column B when looping through collection of worksheets.

Sub FormatAcrossSheets()
Dim currentWS As Worksheet
For Each currentWS In Worksheets
    currentWS.Columns(2).Select             'fails: error "Select method of Range Class failed"
    Selection.Interior.ColorIndex = 17
Next
End Sub

 

[Jarec]

You forgot to activate the sheet
I'd probably recommend suppressing screen updating as well.

[FONT=Courier New]Sub FormatAcrossSheets()
    Dim currentWS As Worksheet
    Application.ScreenUpdating = False
    For Each currentWS In Worksheets
        currentWS.Activate
        currentWS.Columns(2).Select
        Selection.Interior.ColorIndex = 17
    Next
    Application.ScreenUpdating = True
End Sub
[/FONT]

</PRE>

 

[L. Howard]

Hi JohnKauffman,

Try this in a standard module.

Regards,
Howard

Option Explicit

Sub FormatAcrossSheets()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
       With ws.Columns(2)
          .Interior.ColorIndex = 17
       End With
    Next ws

End Sub

 

[JohnKauffman]

Thanks, I really appreciate your taking a few minutes on this.