How do I get macro to change font on all sheets when opening

[ghrek]

Hi

I have this macro that changes the font on the 1st page of a workbook but im trying to do it on all sheets in the workbook. How do I do it?

Private Sub Workbook_Open()
With Sheets(1)
        .Cells.Font.Name = "Arial Narrow"
        .Cells.Font.Size = 11
        .Cells.VerticalAlignment = xlCenter
    End With
End Sub

 

[Max1616]

Hello ghrek, try this:

Private Sub Workbook_Open()
Dim i As Integer

For i = 1 To Sheets.Count

    With Sheets(i)
        .Cells.Font.Name = "Arial Narrow"
        .Cells.Font.Size = 11
        .Cells.VerticalAlignment = xlCenter
    End With
Next i
End Sub

It will loop through each sheet and update the cells formatting.

 

[rollis13]

Use this:

Option Explicit
Private Sub Workbook_Open()
    Dim sht    As Worksheet
    Application.ScreenUpdating = False
    For Each sht In Worksheets
        With sht
            .Cells.Font.Name = "Arial Narrow"
            .Cells.Font.Size = 11
            .Cells.VerticalAlignment = xlCenter
        End With
    Next sht
    Application.ScreenUpdating = True
End Sub

PS. Hi to all. Sorry, didn't update the page .