Insert Borders in Excel Using VBA: Code and Tips

To insert borders with VBA, set the LineStyle, Weight, and optionally Color properties of a range’s Borders collection. For example, the macro below gives cells B2:E8 a thin, dark outline and adds borders between the cells.

A VBA Macro for a Bordered Range

In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the code into the new module, then run the macro while the workbook is active. The range and worksheet are named in the code, so the macro does not depend on which cells happen to be selected.

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

    ' Set the worksheet and the cells to format.
    Set ws = ActiveSheet
    Set target = ws.Range("B2:E8")

    ' Draw the four outside edges.
    With target.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(80, 80, 80)
    End With
    With target.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(80, 80, 80)
    End With
    With target.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(80, 80, 80)
    End With
    With target.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(80, 80, 80)
    End With

    ' Add separators between cells in the range.
    With target.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(80, 80, 80)
    End With
    With target.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(80, 80, 80)
    End With
End Sub

Before running any macro, save a copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, so the backup is useful if the macro formats the wrong range. This example changes formatting only; it does not change cell values.

What the Borders Collection Controls

A range’s Borders collection lets VBA format different sides and divisions of the selected cells. The index identifies which border to change; the properties determine how it looks. In the example, xlEdgeLeft and the other xlEdge constants refer to the range’s perimeter. xlInsideVertical and xlInsideHorizontal refer to dividers between cells in a multi-cell range.

The three properties used most often are:

LineStyle controls whether a border is present and what kind of line it uses. xlContinuous draws a solid line; xlDash and xlDot are other available line styles.
Weight controls thickness. Common constants include xlHairline, xlThin, xlMedium, and xlThick.
Color sets the border color. The example uses RGB(80, 80, 80), a dark gray; ColorIndex is an alternative when working with indexed palette colors.

Set the line style as well as the weight and color. A range may already have formatting, and changing only one property can leave an old style in place or fail to create the intended visible line. To remove a border, set its LineStyle to xlNone.

Choose an Outline, a Grid, or Selected Edges

Decide what visual result you need before choosing border indexes. An outline emphasizes the boundary of a block, while inside borders make the individual cells easier to distinguish. Applying all six edge and inside borders, as in the sample, creates a full grid for a rectangular multi-cell range.

For a simple box with no dividers, use only the four xlEdge borders and leave out the two xlInside sections. For a report with a strong bottom rule beneath its headings, apply xlEdgeBottom to the header row rather than putting a heavy border around every cell. A single-cell range has no internal dividers, so the inside border indexes do not add visible separators there.

Excel also has diagonal border indexes, xlDiagonalDown and xlDiagonalUp, for lines crossing a cell. They are less common in tables, but can be set through the same collection. Avoid using decorative borders everywhere: a light grid for the data and a stronger outline or heading rule often makes a report easier to scan.

Apply Borders to a Range That Changes Size

If a list grows over time, a fixed address such as B2:E8 will not include new rows. You can find the last non-empty row in a reliable column and build the range at run time. This example assumes column A is populated for every record that should receive formatting, and applies borders from A1 through D on that last row.

Sub BorderCurrentList()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim target As Range

    Set ws = ThisWorkbook.Worksheets("Report")

    ' Find the last populated cell in column A.
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Include the header row and columns A through D.
    Set target = ws.Range("A1:D" & lastRow)

    With target.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(160, 160, 160)
    End With
End Sub

Here ThisWorkbook means the workbook containing the macro, and the sheet must be named Report. Change that worksheet name and the column letters to match the file. This approach uses column A to determine the list’s length: if some records have a blank in A, the macro may stop before the end of the data. Choose a column that is consistently filled, or use a different way to identify the data boundary.

Setting target.Borders as a whole is a compact way to apply a consistent border style across the range. For fine control—for example, a heavier outside edge but thin internal dividers—set the edge and inside indexes individually, as in the first macro.

Run the Macro and Save It Correctly

A macro stored in a workbook needs a macro-capable file format. If the workbook contains VBA, save it as an Excel Macro-Enabled Workbook (.xlsm); saving as .xlsx does not preserve VBA code. Use File > Save As and select the macro-enabled format when prompted or when choosing the file type.

To run a procedure from Excel on Windows, press Alt+F8, select its name, and choose Run. You can also use the Visual Basic Editor’s Run command. On Mac, the keyboard shortcuts can depend on the keyboard and function-key settings; use Excel’s Developer tab and its Visual Basic or Macros commands if the Windows shortcut does not apply. The Developer tab may need to be enabled in Excel settings.

Only enable macros in files you trust. Excel may block macros from downloaded files or show a security warning; do not bypass that protection for an unfamiliar workbook. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros. Open the workbook in the desktop Excel application to use this code.

Common Problems and How to Fix Them

The macro affects the wrong cells. Check the worksheet and address assigned to target. A hard-coded address is predictable, but it must match the current layout. Using ActiveSheet can also target a different sheet if another sheet is active; a named worksheet reference is safer for a recurring task.
Only the outside line appears. Confirm that the range contains multiple rows or columns and that the code sets xlInsideHorizontal and/or xlInsideVertical. An outline alone does not add cell-to-cell dividers.
The border looks too faint or thick. Set Weight explicitly and check Color. A pale color can be hard to see on screen or in a printout even when the line style is correct.
The sheet looks unchanged. Worksheet gridlines are not the same as cell borders. Gridlines are a display aid and generally are not printed; borders are cell formatting and can appear in print. Check the selected range and inspect the cells’ formatting rather than relying on gridlines.
The macro will not run. Make sure the file is saved as .xlsm, the procedure is in a standard module, and the macro is permitted by the workbook’s security settings. Excel for the web does not run VBA.

Another useful precaution is to qualify every range with its worksheet, as the second example does. Unqualified references such as Range("A1:D10") depend on the active sheet. A macro can appear to work while formatting a different sheet than intended, particularly when it activates or switches between worksheets.

When VBA Is the Right Tool

VBA is useful when border formatting must be repeated, triggered by a button, or applied to ranges whose size changes. It can also combine borders with other tasks, such as preparing a report before export. For a one-time change, selecting the cells and using Home > Borders is often quicker and avoids enabling macros.

Excel tables are another option for regularly extended datasets. A table can provide consistent formatting as rows are added, although its style is not identical to manually assigning a specific border to every edge. Conditional formatting can change a cell’s appearance when a rule is met, but it is designed for rule-based formatting rather than as a general replacement for a fixed border macro. Choose the simplest method that fits whether the requirement is one-time, automatic, or dependent on cell values.

Frequently Asked Questions

How do I add borders to every cell in a range with VBA?
Set the range’s outside borders and its inside horizontal and vertical borders to a line style such as xlContinuous. The first macro shows all six indexes needed for a rectangular multi-cell grid.

How do I add only an outside border?
Set xlEdgeLeft, xlEdgeTop, xlEdgeRight, and xlEdgeBottom on the target range. Leave out xlInsideHorizontal and xlInsideVertical so there are no internal dividers.

How can I remove borders with VBA?
For a particular side, set that border’s LineStyle to xlNone. To clear the borders from a range, set the line style to xlNone for the relevant border indexes, taking care not to remove borders you want to keep.

Can I undo a border macro with Ctrl+Z?
No. Running a VBA macro clears Excel’s ordinary undo history, so Ctrl+Z will not reverse its formatting changes. Save a separate copy before running a macro, especially when its target range is broad or calculated dynamically.

Will VBA borders work in Excel for the web?
No. Excel for the web does not run VBA. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the macro; the borders already applied to cells can still be viewed in the web app.

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.