CODEWITHSUNDEEP
excelcellvba
user3240191
user3240191

Reputation: 5

Excel Border For All Sheets

I wonder is there a macro to add classic borders for all sheets which containing any text in cells.

I tried with record macro but doesnt work for all sheets.

Sub TheWall()

Application.ScreenUpdating = False
Dim lngLstCol As Long, lngLstRow As Long

lngLstRow = ActiveSheet.UsedRange.Rows.Count
lngLstCol = ActiveSheet.UsedRange.Columns.Count

For Each rngCell In Range("A2:A" & lngLstRow)
    If rngCell.Value > "" Then
        r = rngCell.Row
        c = rngCell.Column
        Range(Cells(r, c), Cells(r, lngLstCol)).Select
            With Selection.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
    End If
Next

Application.ScreenUpdating = True

End Sub

Thanks For Your Help!

Upvotes: 0

Views: 1514

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try this one:

Sub TheWall()

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim rngCell As Range
    Dim hasValue As Boolean
    Dim r As Range

    For Each ws In ThisWorkbook.Worksheets

        For Each rngCell In ws.UsedRange
            hasValue = False
            For Each r In rngCell.MergeArea
                If r.Value <> "" Then
                    hasValue = True
                    Exit For
                End If
            Next r
            If hasValue Then
                With rngCell.Borders
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                    .ColorIndex = xlAutomatic
                End With
            End If

        Next

    Next ws

    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions