CODEWITHSUNDEEP
excelvba
J Dodge
J Dodge

Reputation: 23

Applying formatting border change across all worksheets but one not working?

I have the below code to apply a purple top/bottom border to the last row of every sheet except the one titled "BudgetByMonth" however when I run it it only applies to the sheet I'm currently on. I've been staring at it for ages, can someone help me out?

    Sub FormatLastRow()
Dim ws As Worksheet
Dim LastRow As Long
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "BudgetByMonth" Then
With ws
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeBottom).Weight = xlMedium
    Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeBottom).ColorIndex = 29
    Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeTop).Weight = xlMedium
    Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeTop).ColorIndex = 29
End With
End If
Next ws
End Sub

Upvotes: 0

Views: 63

Answers (2)

Jody Highroller
Jody Highroller

Reputation: 1029

When using a with statement, you need to put a period before the object you are referring to. In your current code you are only referring to the activesheet because you are missing the periods.

Sub FormatLastRow()
    
    Dim ws As Worksheet
    Dim LastRow As Long

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "BudgetByMonth" Then
            With ws
                LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
                .Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeBottom).Weight = xlMedium
                .Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeBottom).ColorIndex = 29
                .Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeTop).Weight = xlMedium
                .Range("A" & LastRow, "BB" & LastRow).Borders(xlEdgeTop).ColorIndex = 29
            End With
        End If
    Next ws

End Sub

Upvotes: 0

braX
braX

Reputation: 11755

Your Range objects are not using the With - put a period before them like this: .Range("A" &

Even better, you can simplify it like this:

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
With ws.Range("A" & LastRow, "BB" & LastRow)
    .Borders(xlEdgeBottom).Weight = xlMedium
    .Borders(xlEdgeBottom).ColorIndex = 29
    .Borders(xlEdgeTop).Weight = xlMedium
    .Borders(xlEdgeTop).ColorIndex = 29
End With

That way, if the "A" or "BB" changes later, you only need to change it in one place.

Upvotes: 2

Related Questions