Apply Border to Cells using VBA in Excel

Today, we will learn to apply borders to cells using VBA in excel. We will learn with the help of examples to apply different border styles in excel with the help of VBA code.

So let us start learning.

VBA Border Property

There are two ways to apply border to cells in VBA. These two methods are explained below.

• Borders property – Using this property, we can apply borders to only one side of the cell.
• BorderAround method – This method applies to the complete cell boundary.

Examples to Apply Border to Cells

In this blog section, we will learn to apply different types of borders to the cells. We will learn the syntax and then implement it with the help of examples.

Example 1 – Apply Border to Bottom of Cell

When we want to apply a border to a range of cells, we need to use the Range property followed by the dot and then the borders property. Thereafter we specify the Line Style for the border.

We can specifically apply border on a specific side of the cell by specifying it in the Borders property.

Follow these steps to do it.

• Press Alt and F11 keys to open the VBE. Go to the Insert tab on the ribbon and take a new module.
• Start entering the following VBA code in the newly inserted module.

Sub exApplyBottomBorder()
'apply border to bottom of cell
Range("A1").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
End Sub

• Use the Range property to specify the range of cells to which you wish to apply the border.

• Use the Dot followed by the Borders Property of Range and specify the border type as xlEdgeBottom to apply the border to the bottom or else the border will be applied to the entire cell boundary.

• Now again use the dot and specify the value of LineStyle to be xlLineStyle.xlContinuous.

• Close the VBE and press Alt and F8 keys to open the list of available macros. Run this Sub procedure.

This is how we apply border to cells using VBA.

Example 2 – Apply a Colored Diagonal Border to the Cell

We will now learn to apply a colored border inside the cell as its diagonal.

• Use the following Sub Procedure to apply the border to range A1:D4.

Sub exApplyColoredDiagonalBorder()
'apply border to first diagonal
With Range("A1:D4").Borders(xlDiagonalDown)
    'specify the linestyle
    .LineStyle = XlLineStyle.xlContinuous
    'specify the color of border
    .Color = RGB(0, 0, 250)
End With
'apply border to second diagonal
With Range("A1:D4").Borders(xlDiagonalUp)
    'specify the linestyle
    .LineStyle = XlLineStyle.xlContinuous
    'specify the color of border
    .Color = RGB(0, 0, 250)
End With
End Sub

When you run this Sub Procedure, a diagonal border of blue color is applied to the range A1:D4 of the active worksheet.

Example 3 – Apply Borders Only to Cells Containing Data using BorderAround

In this example, we will use For Each loop to access each cell of the range and then check if the cell is not empty and then apply the border. If the cell is empty then the border is not applied to it.

We will use the ISEMPTY Function to check if a cell is empty.

Use the following Sub Procedure to apply the border to a cell in range A1:A10 if it is not empty.

Sub exApplyBorderToNonEmptyCells()
'apply border to non empty cells using BorderAround Property
Dim myCell As Range
Dim myrange As Range
Set myrange = Range("A1:A10")
For Each myCell In myrange
    If IsEmpty(myCell) Then
    'nothing happens
    Else
    'specify the linestyle, border weight as thick and color of border
    myCell.BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=RGB(250, 0, 0)
    End If
Next
End Sub

As a result, when you run this Sub procedure, it would check if each cell in range A1:A10 is nonempty and then apply the border to it using the BorderAround Property.

This brings us to an end.

Thank you for reading.