Excel VBA: Update the formatting for all worksheets [duplicate]

Question

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:

Loop through all the worksheets (visible only)
Set bold format to the top row, and set the background to grey
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

Hambone · Accepted Answer · 2016-02-01 01:09:18Z

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

edited Feb 1, 2016 at 1:09

answered Feb 1, 2016 at 0:51

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

George Over a year ago

What about if the selection of the worksheet is not A1? As the previous coworker sometimes worked and saved their last position.

Hambone Over a year ago

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.

George Over a year ago

I think that is what I need! Thanks Hambone!

Collectives™ on Stack Overflow

Excel VBA: Update the formatting for all worksheets [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related