Apply formatting to all sheets except the last sheet.

[Harshil Mehta]

Expectation: Apply formatting to all sheets in one go.

Problem: The Below code applies borders to the active sheet only instead of all the sheets (Except DASHBOARD sheet). I dont understand where I am going wrong.

Sub Format_all_sheets()

Dim sht As Object
Dim lcol, lrow As Long


For Each sht In Sheets
If sht.Name <> "Dashboard" Then
                   
With sht

lcol = Cells(10, Columns.Count).End(xlToLeft).Column
lrow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row


With Range(Cells(10, 2), Cells(lrow, lcol))
    .Borders.LineStyle = xlcontinous
    .Borders.Color = vbBlack
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With


End With

End If

Next

End Sub

 

[Fluff]

When using a with statement anything inside it that refers to the with must be preceded by a full stop like

Sub Format_all_sheets()

Dim sht As Object
Dim lcol, lrow As Long


For Each sht In Sheets
If sht.Name <> "Dashboard" Then
                   
With sht

lcol = .Cells(10, .Columns.Count).End(xlToLeft).Column
lrow = .Cells.Find(What:="*", _
                    After:=.Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row


With .Range(.Cells(10, 2), .Cells(lrow, lcol))
    .Borders.LineStyle = xlcontinous
    .Borders.Color = vbBlack
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With


End With

End If

Next

End Sub

 

[Harshil Mehta]

When using a with statement anything inside it that refers to the with must be preceded by a full stop like

Sub Format_all_sheets()

Dim sht As Object
Dim lcol, lrow As Long


For Each sht In Sheets
If sht.Name <> "Dashboard" Then
                  
With sht

lcol = .Cells(10, .Columns.Count).End(xlToLeft).Column
lrow = .Cells.Find(What:="*", _
                    After:=.Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row


With .Range(.Cells(10, 2), .Cells(lrow, lcol))
    .Borders.LineStyle = xlcontinous
    .Borders.Color = vbBlack
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With


End With

End If

Next

End Sub

Thank you for you guidance.

 

[Fluff]

You're welcome & thanks for the feedback.