【Excel】Macro to change the thickness of all borders set on a sheet

Adjusting the thickness of all borders within a worksheet in Excel can be a time-consuming and tedious task. In this article, we will discuss a macro that allows you to change the thickness of all borders within a worksheet in Excel in bulk.

The macro we have created this time is designed to change the thickness of borders set on a single sheet to the ‘thickest line’ in bulk. While it is easy to modify the border thickness for table-style borders using regular functions, it becomes a manual process to modify borders that are set inconsistently across a sheet, such as in a family tree or organizational chart. This can be very time-consuming.

By automating this process, you can save valuable time and effort.

Macro to change the thickness of all borders set on a sheet

Sub AdjustGridlineThickness()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastCell As Range
    Dim cell As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name

Application.ScreenUpdating = False
    ' Find the last cell with data in the worksheet
    Set lastCell = ws.Cells.SpecialCells(xlLastCell).Cells

    If Not lastCell Is Nothing Then

        ' Define the range to validate based on the last cell with data
        Set rng = ws.Range("A1", lastCell)

        ' Iterate through each cell in the defined range
        For Each cell In rng
            ' Check if the cell has existing gridlines
            If cell.Borders(xlEdgeLeft).LineStyle <> xlNone Then
                cell.Borders(xlEdgeLeft).Weight = xlThick ' Adjust the thickness for left gridline
            End If

            If cell.Borders(xlEdgeTop).LineStyle <> xlNone Then
                cell.Borders(xlEdgeTop).Weight = xlThick ' Adjust the thickness for top gridline
            End If

            If cell.Borders(xlEdgeRight).LineStyle <> xlNone Then
                cell.Borders(xlEdgeRight).Weight = xlThick ' Adjust the thickness for right gridline
            End If

            If cell.Borders(xlEdgeBottom).LineStyle <> xlNone Then
                cell.Borders(xlEdgeBottom).Weight = xlThick ' Adjust the thickness for bottom gridline
            End If
        Next cell
    End If
Application.ScreenUpdating = True

End Sub

• Please remember to back up your data before executing this macro.
• Replace ‘sheet1’ with the actual name of the sheet you intend to use.

This macro will change all borders within the sheet to the specified thickness in bulk. For example, it will change the borders from their original thickness (thin lines) to the thickest line.

Types of border thickness

There are four types of border thickness. They can be represented as ‘Weight = xlThick’ and you can freely set the thickness by replacing ‘xlThick’ with any of the following options:

In conclusion

Please make use of this macro when dealing with situations where there are random variations in the thickness of borders, such as in organizational charts.

Creating a macro allows you to save more time compared to manual work.