VBA to Add Border to Cells In Excel

You can add borders to cells in a range to improve the visual presentation of data. However, manually adding borders to cells in a range can be time-consuming.

You can use VBA to simplify the process and save time and effort.

In this tutorial, I will use eight examples to show you how to use the ‘Range.Borders‘ property and ‘Range.BorderAround‘ method in VBA to add borders to cells in a range.

‘Range.Borders’ Property in VBA

The ‘Range.Borders’ property of the ‘Range’ object returns a ‘Borders’ object with several properties you can use to modify different borders, such as line style, color, and thickness.

The syntax of the ‘Range.Borders’ property is as follows:

expression.Borders

where, expression is a variable representing a ‘Range’ object.

Constants that Specify Border Positions

In the table below are the various constants that you can use in conjunction with the ‘Borders‘ object to specify different border positions:

Constant	Description
xlEdgeBottom	Bottom border
xlEdgeTop	Top border
xlEdgeLeft	Left border
xlEdgeRight	Right border
xlInsideHorizontal	Horizontal borders (between rows)
xlInsideVertical	Vertical borders (between columns)
xlDiagonalUp	Border from top-right to bottom-left of cell
xlDiagonalDown	Border from top-left to bottom-right of cell

The ‘Range.Borders.LineStyle’ Property

In the table below are some commonly used constants and their values that you can assign to the ‘Range.Borders.LineStyle‘ property to specify various line styles:

Constant	Index Value	Description
xlContinuous	1	Solid line
xlDash	-4115	Dashed line
xlDashDot	4	Alternating dashes and dots
xlDashDotDot	5	Alternating dashes and double dots
xlDot	-4118	Dotted line
xlDouble	-4119	Double line
xlSlantDashDot	13	Slanted dashes and dots
xlLineStyleNone	-4142	No line

The ‘Range.Borders.Weight’ Property

Below are some commonly used constants and their values that you can assign to the ‘Range.Borders.Weight‘ property to specify various line thicknesses:

Constant	Index Value	Description
xlHairline	1	Hairline thickness
xlThin	2	Thin line
xlMedium	-4138	Medium line
xlThick	4	Thick line

The ‘Range.Borders.ColorIndex’ Property

Below are some commonly used constants and their values that you can assign to the ‘Range.Borders.ColorIndex‘ property to specify various line colors:

Constant (RGB Value)	Index Value	Description
vbBlack	1	Black
vbWhite	2	White
vbRed	3	Red
vbGreen	4	Green
vbBlue	5	Blue
vbYellow	6	Yellow
xlColorIndexAutomatic	16777215	Excel automatically determines color based on the current theme or default settings.

Note: You can also use the RGB function to set the ‘ColorIndex’ property. 

The RGB function creates a color value by blending different red, green, and blue intensities. The function takes three integer arguments, each ranging from 0 to 255, representing the intensity of the corresponding color component.

By combining these values, you can create a specific color. For example, RGB(255,0,0) creates a bright red color.

Also read: How to Bold Text using VBA in Excel

Example #1: Add a Border to a Single Cell

Suppose you have the dataset below on Sheet1:

You can add a border to cell B6 containing the discount percentage using the code below:

Sub AddBorderToCell()
With Worksheets("Sheet1").Range("B6").Borders
    .LineStyle = xlContinuous
    .Color = vbBlack
    .Weight = xlThin
End With
End Sub

When you execute the subroutine, it adds a border to cell B6:

Explanation of the VBA Code

The code uses the ‘With…End With’ construct to add a black, continuous, and thin border to cell B6 by modifying the ‘LineStyle,’ ‘Color,’ and ‘Weight’ properties of the ‘Borders’ object.

Example #2: Add Bottom Border to Range

Suppose you have the dataset below on Sheet2.

You can use VBA to add a thin black border at the bottom of the cell range A1:C1 using the steps below:

Sub SetBottomEdgeRangeBorder()
 With Worksheets("Sheet2").Range("A1:C1").Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlThin
 .ColorIndex = vbBlack
 End With
End Sub

When you run the subroutine, it adds a thin red border at the bottom of the cell range A1:F1:

Explanation of Code

The subroutine in this example utilizes the ‘xlEdgeBottom’ constant in conjunction with the ‘Range.Borders’ property and sets the bottom border of the specified range to a continuous, thin, black line. 

Example #3: Add Borders to the Current Region

You can use VBA to add borders to the current region of the active cell. The current region is the block of cells surrounded by blank rows and columns.

Assuming your dataset is located on the active sheet, with the active cell being C5: 

You can use VBA to add thin black borders to the current region using the code below:

Sub ApplyBordersToCurrentRegion()
Dim currentRegion As Range
Set currentRegion = Selection.currentRegion
    With currentRegion.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = vbBlack
    End With
End Sub

When you execute the code, it applies borders to the current region:

Explanation of the VBA Code

The example code utilizes the ‘Selection.CurrentRegion’ property to identify the range of cells that forms a continuous block around the active cell.

The code then uses the ‘With…End With’ construct to set the line style of the borders to continuous, the border weight to thin, and the border color to automatic, using the default color for borders.

Example #4: Add Borders to the Used Range in Active Worksheet

You can use VBA to add borders to the Used Range of the active worksheet.

The used range includes all the cells used in the worksheet, starting from the first cell with a value and ending at the last cell with a value.

Suppose you have the data below on the active worksheet:

You can use VBA to add thin black borders to the used range using the VBA code below:

Sub ApplyBordersToUsedRange()
Dim usedRange As Range
Set usedRange = ActiveSheet.usedRange
With usedRange.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = vbBlack
End With
End Sub

When you execute the code subroutine, it applies borders to the used range:

Explanation of the VB Code

The subroutine uses the ‘With…End With’ construct to apply a continuous, thin, black border to all cells within the used range of the active sheet by modifying the ‘LineStyle,’ ‘Color,’ and ‘Weight’ properties of the ‘Borders’ object.

Example #5: Add Borders to Selected Range

You can use VBA to add borders to a selected cell range.

Suppose you have the data below on the active worksheet:

You can select the cell range A1:C7 and use the VBA code below to add thin black borders to the selected range:

Sub ApplyBordersToSelectedRange()
Dim selectedRange As Range
Set selectedRange = Selection
With selectedRange.Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = vbBlack
End With
End Sub

When you run the code, it applies borders to the selected range:

Explanation of the Code

The code applies a continuous, thin, black border to the cells within the selected range on the active worksheet by modifying the ‘LineStyle,’ ‘ColorIndex,’ and ‘Weight’ properties of the ‘Borders’ object.

Also read: How to Change Cell Color Using VBA in Excel

Example #6: Add Borders of Different Styles to Cell Range

Suppose you have the below dataset on Sheet6:

You can use VBA to add thin black borders at the bottom of cell ranges A1:C1 and A5:C5 and a thin black double border at the bottom of cell range A6:C6 using the code below:

Sub AddBordersOfDifferentStyles()

 With Worksheets("Sheet6").Range("A1:C1").Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 1
 End With

 With Worksheets("Sheet6").Range("A5:C5").Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 1
 End With

 With Worksheets("Sheet6").Range("A6:C6").Borders(xlEdgeBottom)
    .LineStyle = xlDouble
    .Weight = xlThick
    .ColorIndex = 1
 End With

End Sub

When you execute the code, it applies borders of different styles to the specified cell ranges:

Explanation of the Code

The code utilizes the ‘Borders’ property with different styles, weights, and colors to apply custom borders to specific cell ranges on Sheet6. The first two ‘With…End With’ blocks apply a continuous, thin border, and the third block applies a double, thick border to the specified ranges.

‘Range.BorderAround’ Method

The ‘Range.BorderAround’ method is used in VBA to apply a border around a specified range of cells in an Excel worksheet and set the ‘Color,’  ‘LineStyle,’ and ‘Weight’ properties of the ‘Border’ object for the new border. 

The ‘Range.BorderAround’ method allows you to add a border that surrounds the entire range, providing a convenient way to highlight or distinguish a specific block of cells.

The basic syntax of the method is as follows:

expression.BorderAround(LineStyle, Weight, ColorIndex, Color, ThemeColor)

expression is a variable that represents the ‘Range’ object.

The ‘Range.BorderAround’ method has four optional arguments, as shown below:

Argument	Description
LineStyle	Specifies the line style of the border. Can be set to any of the constants or values described in the ‘ The Range.Borders.LineStyle Property.’
Weight	The thickness of the border.
ColorIndex	The border color.
Color	The border color is set to an RGB value.
ThemeColor	The theme color. Can be set to xlThemeColorAccent1(index value 5), xlThemeColorDark1 (index value 1), etc.

Example #7: Add a Border Around a Specific Range

Suppose you have the dataset below on Sheet7:

You can use the VBA code below to add a thick blue border around the cell range B2:D6 using the steps below:

Sub SetAroundBorder()
Worksheets("Sheet7").Range("B2:D6").BorderAround _
 Color:=vbBlue, Weight:=xlThick
End Sub

When you run the subroutine, it adds a thick blue border around the cell range B2:D6:

Explanation of the Code

The ‘Color’ and the ‘Weight’ arguments of the ‘Range.BorderAround’ method are set to ‘vbBlue’ and ‘xlThick’ respectively to produce a thick blue border around the cell range B2:D6.

Example #8: Add Border Around a Selected Range

Suppose you have the dataset below on the active worksheet:

You can select the dataset and use the VBA code below to add a thick green border around the dataset:

Sub ApplyBordersAroundSelectedRange()
Dim selectedRange As Range
Set selectedRange = Selection
selectedRange.BorderAround _
LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbGreen  
End Sub

When you execute the code, it places a thick green border around the selected range:

Explanation of the VBA Code

The ‘Color’ and the ‘Weight’ arguments of the ‘Range.BorderAround’ method are set to ‘vbGreen’ and ‘xlThick’ respectively to produce a thick green border around the cell range B2:D6.

You can also modify the code to add a border around the used range, current region, etc.

Benefits of Applying Borders to Cell Ranges

Adding borders to cell ranges provides several benefits, including the following:

1. Borders help you draw attention to or highlight important data points of your data.
2. Borders help you structure your data, making it more organized and readable.
3. Borders give a professional look to your reports, invoices, and other data.

In this article, I showed you eight examples of using the ‘Range.Borders‘ property and ‘Range.BorderAround‘ method in VBA to add borders to cells in a range.

I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What does VBA mean in Excel?
• VBA to Print Range of Cells to PDF
• VBA to Copy Range to Email Body
• Bottom Border Shortcut in Excel
• How to Remove Borders in Excel?