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:

ConstantDescription
xlEdgeBottomBottom border
xlEdgeTopTop border
xlEdgeLeftLeft border
xlEdgeRightRight border
xlInsideHorizontalHorizontal borders (between rows)
xlInsideVerticalVertical borders (between columns)
xlDiagonalUpBorder from top-right to bottom-left of cell
xlDiagonalDownBorder 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:

ConstantIndex ValueDescription
xlContinuous1Solid line
xlDash-4115Dashed line
xlDashDot4Alternating dashes and dots
xlDashDotDot5Alternating dashes and double dots
xlDot-4118Dotted line
xlDouble-4119Double line
xlSlantDashDot13Slanted dashes and dots
xlLineStyleNone-4142No 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:

ConstantIndex ValueDescription
xlHairline1Hairline thickness
xlThin2Thin line
xlMedium-4138Medium line
xlThick4Thick 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 ValueDescription
vbBlack1Black
vbWhite2White
vbRed3Red
vbGreen4Green
vbBlue5Blue
vbYellow6Yellow
xlColorIndexAutomatic16777215Excel 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:

VBA to Add Border to Cells In Excel - Single Cell

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:

VBA to Add Border to Cells In Excel - Single Cell result

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.

VBA to Add Border to Cells In Excel - Range

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:

VBA to Add Border to Cells In Excel - Range Result

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: 

VBA Add Border Current Range

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:

VBA Add Border Current Range Result

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:

VBA Add Border Used Range

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:

VBA Add Border Used Range Result

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:

VBA Add Border Used Range

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:

VBA Add Border Selected Range Result

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:

VBA to Add Different Border Styles to Cells

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:

VBA Add Different Border Styles to Cells Result

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:

ArgumentDescription
LineStyleSpecifies the line style of the border. Can be set to any of the constants or values described in the ‘ The Range.Borders.LineStyle Property.’
WeightThe thickness of the border. 
ColorIndexThe border color.
ColorThe border color is set to an RGB value.
ThemeColorThe 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:

Add a Border Around a Specific Range Dataset

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:

Add Blue Border Around a Specific Range

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:

Add border around specific range VBA

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:

Add border around specific range VBA Resulr

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.