VBA borders around cells and ranges

In Excel, you can use VBA to draw borders around cells, ranges and selected ranges.

First, open the VBA Editor (Alt + F11) to insert code.

Borders around cells and ranges

Let’s draw some border around a single cell. In the example, we are going to specify exactly which cell we want to use.

Sub DrawBorderAroundRange()
Range("B2").BorderAround ColorIndex:=1
End Sub

This is the simplest code to draw a border around all edges at once.

You can also do it for a range.

Range("B2:C3").BorderAround ColorIndex:=1

Borders around cells and ranges

You can use the following code in order to make borders around the currently selected cell(s).

Sub DrawBorderAroundSelection()
Selection.BorderAround ColorIndex:=1
End Sub

But if you try to do it around more than a single cell, you are going to get the following result.

Additional parameters

We can modify this code a bit in order to give it additional options, such as line color and thickness.

Selection.BorderAround ColorIndex:=4, Weight:=xlThick

The following code will result in the following borders.

If you want to use color from drawing application, where the value is written as a hexadecimal number. First, you have to convert this value into decimals.

You can do it using the following code.

Sub DrawBorderAroundSelection()
    Dim decValue As Double
    Dim hexValue As String
    hexValue = "CCF209"
    decValue = Val("&H" + hexValue)
    Selection.BorderAround Color:=decValue, Weight:=xlThick
End Sub

And this is the result.

Posted in vba