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 likexlEdgeLeft(which is 7). - Borders not appearing because the
LineStyleis set toxlNoneafter setting other properties. Always setLineStylelast. - Border color not applying because you used
ColorIndexandColortogether. 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.ProtectwithUserInterfaceOnly:=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
- Excel Spreadsheet to Track Job Applications? Yes, Here’s How
- Excel Spreadsheet for Inventory Tracking: How to Build One
- Employee Schedule Spreadsheet: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Copy and Paste a Dynamic Range AutomaticallyTo copy a range that grows or shrinks, use VBA to find its last populated row and column,…
- Excel VBA MsgBox Carriage Return: Line Breaks ExplainedTo put a carriage return and line break in an Excel VBA MsgBox, join the message parts with…
- Excel VBA Format Cell Bold: The Complete GuideTo bold a cell in Excel VBA, use the Font.Bold property: Range("A1").Font.Bold = True. This single line of…
- Pivot Table Refresh From VBA: Code and Common FixesTo refresh a PivotTable with VBA in Excel for Microsoft 365 on Windows, call its RefreshTable method, such…
- Excel VBA Range: How to Select, Read, and Edit CellsIn Excel VBA, a Range is an object that refers to one cell, a group of cells, or…
- VBA Loops Within Loops: How Nested Loops Work in ExcelYes. VBA can run one loop inside another; this is called a nested loop. The outer loop runs…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.