VBA Add Border to a Range: Code, Styles, and Fixes

VBA & macros · Updated November 18, 2026

To add borders to a range in Excel VBA, set the range’s Borders properties. For example, Range("B2:F10").Borders.LineStyle = xlContinuous applies solid borders; you can also set their weight and color, or target particular edges.

How VBA range borders work

A cell border is a formatting property, separate from the cell’s value, number format, and fill color. In VBA, you address it through a Range object’s Borders collection. Setting a property on that collection lets you format the border positions in the range at once, rather than looping through each cell.

For a range with multiple rows or columns, the collection can include the outer edges and the internal horizontal and vertical lines between cells. Excel provides named constants for these positions, including xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideHorizontal, and xlInsideVertical. Those constants make code easier to read than using numeric border indexes.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is useful if the code formats the wrong cells.

Apply the same border to every cell edge

For a quick grid, set the line style, weight, and color for the range’s Borders collection. This example uses a named worksheet and a fixed range, so it does not depend on whichever cells happen to be selected:

Sub AddGridBorders()
    Dim ws As Worksheet
    Dim target As Range

    ' Set the worksheet and cells to format.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("B2:F10")

    ' Apply solid, thin, dark gray borders.
    With target.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(80, 80, 80)
    End With
End Sub

Change "Report" to the exact name of a worksheet in the workbook, and adjust "B2:F10" to your target cells. RGB(80, 80, 80) creates a dark gray color; use RGB(0, 0, 0) for black. This code formats all applicable border positions in the range, including internal dividers in a multi-cell range.

Format only the outside border

A table or report block often needs a border around its perimeter without lines between every cell. In that case, set the four edge borders individually. The following example uses a heavier blue outline and leaves the interior border formatting untouched:

Sub AddOutlineBorder()
    Dim ws As Worksheet
    Dim target As Range
    Dim edge As Variant

    ' Set the worksheet and range to outline.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("B2:F10")

    ' Format each outside edge.
    For Each edge In Array(xlEdgeLeft, xlEdgeTop, _
                           xlEdgeBottom, xlEdgeRight)
        With target.Borders(edge)
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .Color = RGB(31, 78, 121)
        End With
    Next edge
End Sub

Use this approach when an outline should distinguish a block while preserving existing internal lines. For an especially simple macro, you can write a separate With target.Borders(xlEdgeLeft) block for each side instead of using a loop. The loop is shorter when all four edges share the same appearance.

Set internal lines or style individual edges

To add dividers inside a multi-cell range, format the internal border positions explicitly. This is useful when a range needs thin internal lines but a more prominent outside edge:

Sub AddGridWithStrongOutline()
    Dim ws As Worksheet
    Dim target As Range

    ' Define the report block.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("B2:F10")

    ' Add thin internal dividers.
    With target.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(191, 191, 191)
    End With
    With target.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(191, 191, 191)
    End With

    ' Make the four outside edges heavier.
    With target.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .Color = RGB(0, 0, 0)
    End With
    With target.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .Color = RGB(0, 0, 0)
    End With
    With target.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .Color = RGB(0, 0, 0)
    End With
    With target.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .Color = RGB(0, 0, 0)
    End With
End Sub

Internal borders make sense for ranges with more than one row or column. For a single-cell range, there are no internal dividers to display. You can also set only one edge—for instance, target.Borders(xlEdgeBottom)—to draw a separator beneath a heading or subtotal row.

Choose line style, weight, and color

Border appearance is controlled by several properties, and setting them together makes results more predictable. Common choices include:

  • LineStyle controls whether the border is continuous, dashed, dotted, or absent. For a standard solid line, use xlContinuous.
  • Weight controls thickness. Typical constants are xlHairline, xlThin, xlMedium, and xlThick.
  • Color sets an RGB color, such as RGB(0, 0, 0) for black. ColorIndex is an alternative based on Excel’s color palette.

For example, a dotted bottom edge can use .LineStyle = xlDot, while a dashed edge can use .LineStyle = xlDash. Choose the style constants that match the appearance you need; do not rely on assigning a weight alone to create a visible line. A border with LineStyle = xlNone is removed, regardless of its previous color or weight.

Remove borders or change an existing format

To clear borders from a range, set its border line style to xlNone:

Sub RemoveRangeBorders()
    Dim ws As Worksheet
    Dim target As Range

    ' Specify the cells whose borders should be cleared.
    Set ws = ThisWorkbook.Worksheets("Report")
    Set target = ws.Range("B2:F10")

    ' Remove the range's border lines.
    target.Borders.LineStyle = xlNone
End Sub

This clears borders across the selected range, including internal lines. If you only want to remove one edge or the inside dividers, address that border position instead, such as target.Borders(xlInsideHorizontal).LineStyle = xlNone. Be deliberate about the target: applying a clearing command to a whole worksheet can remove formatting you meant to keep.

Run the macro safely in Excel

These examples are for desktop Excel for Microsoft 365 on Windows. To add and run a macro:

  1. Save a backup copy of the workbook, then save the working file as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain VBA code.
  2. Open the Visual Basic Editor with Alt+F11. On a Mac, the shortcut is usually Option+F11; some keyboards require Fn as well. You can also use the Developer tab to open Visual Basic. If the Developer tab is hidden in Windows, enable it through File > Options > Customize Ribbon.
  3. In the Visual Basic Editor, choose Insert > Module, paste the procedure into the module, and update the sheet name and range.
  4. Return to Excel and run it from Developer > Macros, or run the procedure from the editor. If Excel blocks the macro, review the workbook’s trusted-source and macro security settings rather than enabling unknown code indiscriminately.

Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use the Excel desktop application for this code. VBA is available in desktop Excel for Mac as well, though menus and keyboard shortcuts can differ; the VBA border properties used here are generally the same.

Common problems and how to fix them

If the code runs but the border seems missing, check that the worksheet name and range are correct and that the range is not hidden or outside the visible area. Also confirm that LineStyle is not set to xlNone. A very light border color or xlHairline weight can be difficult to see, especially on screen.

Other common causes include:

  • Formatting the wrong cells: A hard-coded range such as B2:F10 may not match the current report layout. Verify the address, or calculate the range deliberately when the data size changes.
  • Unexpected worksheet: Unqualified references such as Range("B2:F10") can refer to the active sheet. Qualifying the range with a worksheet variable avoids depending on which sheet is active.
  • Protected worksheet: Sheet protection can prevent formatting changes, depending on how protection was applied. Check the protection settings and permissions before changing the macro.
  • Conditional formatting: A conditional formatting rule can affect the displayed appearance of cells. If a border looks different from the VBA formatting, inspect the applicable rules in Home > Conditional Formatting > Manage Rules.
  • Macro not running: Excel for the web does not run VBA. In desktop Excel, confirm that the workbook is macro-enabled and that the macro is permitted by your organization’s security policy.

When diagnosing a formatting issue, test on a small, known range in a copy of the workbook. This helps distinguish a VBA problem from a sheet protection, conditional formatting, or range-selection issue.

Frequently Asked Questions

How do I add a border to a range in VBA?
Set a property on the range’s Borders collection. For a solid border on all applicable edges, use target.Borders.LineStyle = xlContinuous; set Weight and Color too if you need a particular appearance.

How do I add only an outside border in Excel VBA?
Format target.Borders(xlEdgeLeft), xlEdgeTop, xlEdgeBottom, and xlEdgeRight individually. This leaves the internal horizontal and vertical border positions alone.

How can I add borders between cells in a range?
Set the properties of target.Borders(xlInsideHorizontal) and target.Borders(xlInsideVertical). These positions create dividers within a multi-row or multi-column range.

How do I remove a border with VBA?
Set the relevant border’s LineStyle to xlNone. Use target.Borders.LineStyle = xlNone to clear all border positions for the range, or specify one edge or internal position to remove only that part.

Can I undo a VBA border change with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its formatting changes. Save a separate workbook copy before running code that changes borders.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.