Monday, July 29, 2019

Excel VBA: Update the formatting for all worksheets




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

Answer



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


No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...