2

I read through a few online tutorials, and use the macro record to learn how to set formats. But I am wondering is there a way to do the following, without using .Select? Or what is the preferred way by programmers?

Requirement for the simple macro:

  1. Loop through all the worksheets (visible only)
  2. Set bold format to the top row, and set the background to grey
  3. Reset the selection to A1 position

()

Sub SetAllTopRowBold()
Dim ws As Worksheet
On Error Resume Next

For Each ws In ThisWorkbook.Worksheets
    If ws.Visible Then
        ws.Activate
        Rows(1).Select
        Selection.Font.Bold = True
        Selection.Interior.Color = RGB(190, 190, 190)
        Range("A1").Select
    End If
Next ws
End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
George
  • 4,514
  • 17
  • 54
  • 81

1 Answers1

3

You can do it directly against the range object:

For Each ws In ThisWorkbook.Worksheets
    If ws.Visible Then
        ws.Rows(1).Font.Bold = True
        ws.Rows(1).Interior.Color = RGB(190, 190, 190)
        ws.Select
        ws.Range("A1").Select
    End If
Next ws
Hambone
  • 15,600
  • 8
  • 46
  • 69
  • What about if the selection of the worksheet is not A1? As the previous coworker sometimes worked and saved their last position. – George Feb 01 '16 at 00:55
  • In my haste, I didn't see that part. In that case, I think your current approach is actually the way to go, except add `ws.` since you're not actually switching sheets within the code: `ws.Range("A1").Select`. I added that to my answer. – Hambone Feb 01 '16 at 00:57
  • I think that is what I need! Thanks Hambone! – George Feb 01 '16 at 01:00