Excel VBA Cell Border: How to Add, Remove, and Format

VBA & macros · Updated November 22, 2026

To add a border to a cell in Excel VBA, use the BorderAround method or set the Borders collection properties. For example, Range("A1").BorderAround LineStyle:=xlContinuous, Weight:=xlThin adds a thin outline around cell A1. This article explains the full syntax, how to control individual border edges, common pitfalls, and practical examples.

Understanding the Borders Collection

Every Excel Range object has a Borders collection that contains seven individual border objects: xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom, xlInsideVertical, xlInsideHorizontal, and xlDiagonalDown/xlDiagonalUp. You can modify each border’s LineStyle, Weight, and Color properties individually, or apply a uniform border using BorderAround.

For a multi-cell range, the “Edge” borders apply to the outer edges of the entire range, while the “Inside” borders apply to internal gridlines. This distinction is crucial when formatting a table-like block of cells.

Basic Syntax: BorderAround vs. Borders Property

The BorderAround method is the quickest way to add an outline to a range. It takes three optional parameters: LineStyle, Weight, and Color. Here’s a simple example:

Sub AddOutlineBorder()
    Range("A1:D10").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, Color:=RGB(0, 0, 0)
End Sub

To control individual edges, use the Borders collection with an index. For example, to add a thick bottom border to row 1:

Sub AddBottomBorder()
    With Range("A1:D1")
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).Weight = xlThick
        .Borders(xlEdgeBottom).Color = RGB(255, 0, 0)
    End With
End Sub

Note: When using BorderAround, the Color parameter expects an RGB value or a color constant like vbRed. If you omit Color, it defaults to black.

Removing Borders

To remove all borders from a range, set each border’s LineStyle to xlNone. You can loop through the Borders collection or use BorderAround with LineStyle:=xlNone. Here’s a robust way:

Sub RemoveAllBorders()
    Dim cell As Range
    For Each cell In Range("A1:D10")
        cell.Borders.LineStyle = xlNone
    Next cell
End Sub

Alternatively, for a single range (not each cell), you can set Range("A1:D10").Borders.LineStyle = xlNone directly, which clears all borders on that range. The loop is useful if you want to clear borders cell-by-cell for conditional formatting.

Applying Borders to a Range of Cells (Loops vs. Direct)

When you apply a border to a multi-cell range, Excel treats the range as a single block. For example, Range("A1:C3").BorderAround draws a box around the entire block, not around each cell. To border each cell individually, you must loop through each cell:

Sub BorderEachCell()
    Dim rng As Range
    Set rng = Range("A1:C3")
    Dim cell As Range
    For Each cell In rng
        cell.BorderAround LineStyle:=xlContinuous, Weight:=xlThin
    Next cell
End Sub

This is a common source of confusion. If you want a grid where every cell has its own outline, use the loop. If you want a single rectangle around the whole range, use BorderAround on the range directly.

Line Styles, Weights, and Colors

Excel VBA offers several line styles: xlContinuous, xlDash, xlDot, xlDashDot, xlDashDotDot, xlDouble, and xlSlantDashDot. The Weight property accepts xlHairline, xlThin, xlMedium, and xlThick. Colors can be set using ColorIndex (e.g., ColorIndex = 3 for red) or Color with RGB.

Here’s an example that creates a dashed red border:

Sub DashedRedBorder()
    With Range("B2:E5")
        .BorderAround LineStyle:=xlDash, Weight:=xlMedium, Color:=RGB(255, 0, 0)
    End With
End Sub

Remember that xlDouble only works with xlThin or xlThick weights; other combinations may produce unexpected results.

Common Errors and How to Fix Them

  • “Subscript out of range” when using Borders(xlEdgeLeft) on a multi-cell range? Actually, that error usually occurs if you use an invalid index. Ensure you use the constants like xlEdgeLeft (which is 7).
  • Borders not appearing because the LineStyle is set to xlNone after setting other properties. Always set LineStyle last.
  • Border color not applying because you used ColorIndex and Color together. Use one or the other.
  • Macro runs but no border shows if the range is on a protected sheet. Unprotect the sheet first or use Worksheet.Protect with UserInterfaceOnly:=True.

Performance Tips for Large Ranges

Applying borders to thousands of cells with a loop can be slow. To speed up, use Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual before the loop, then restore them after. Also, consider applying borders to the entire range at once if you want uniform edges, and only loop when each cell needs a different style.

Sub FastBorders()
    Application.ScreenUpdating = False
    Dim rng As Range
    Set rng = Range("A1:F1000")
    rng.Borders.LineStyle = xlContinuous
    rng.Borders.Weight = xlThin
    Application.ScreenUpdating = True
End Sub

This sets all borders (inside and outside) to a thin continuous line in one operation, which is much faster than looping.

Frequently Asked Questions

How do I add a border to only the used range of a worksheet?

Use ActiveSheet.UsedRange.BorderAround to add an outline around the entire used area. To border each cell within the used range, loop through UsedRange.Cells.

Can I use a macro to add borders based on cell values?

Yes. In a loop, check the cell’s value (e.g., If cell.Value > 100 Then) and apply a border conditionally. This is a manual alternative to conditional formatting, but conditional formatting is usually more efficient for live updates.

Why does my border disappear when I filter or sort?

Borders are attached to cells, not to data. When you filter, rows are hidden but borders remain. Sorting moves the data but borders stay with the original cells, so the visual pattern may change. To keep borders with data, you’d need to reapply after sorting, or use a Table (ListObject) which maintains formatting.

Is there a way to copy only borders from one range to another?

Yes, use Range.Copy with PasteSpecial Paste:=xlPasteFormats. For example: Range("A1:D1").Copy then Range("A2:D2").PasteSpecial Paste:=xlPasteFormats. This copies all formatting, including borders.

How do I remove only the inside borders but keep the outline?

Set xlInsideHorizontal and xlInsideVertical to xlNone while keeping the edge borders. Example: Range("A1:C3").Borders(xlInsideHorizontal).LineStyle = xlNone and similarly for vertical.

Does the BorderAround method work on a single cell?

Yes, it works on any range, including a single cell. It draws a box around that cell.

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.