In this article I will explain how you can make basic borders using VBA for Excel. Each range of cells can accept up to 8 different types of borders:

1. Left edge (xlEdgeLeft)
2. Top edge (xlEdgeTop)
3. Bottom edge (xlEdgeBottom)
4. Right edge (xlEdgeRight)
5. Inside vertical (xlInsideVertical)
6. Inside horizontal (xlInsideHorizontal)
7. Diagonal down (xlDiagonalDown)
8. Diagonal up (xlDiagonalUp)

I have also provided a complete sample here.

You can download the code and file for the sample here.

Left edge (xlEdgeLeft):

The code below will create a simple border for the left edge of the range “B2:D4”:

Range("B2:D4").Borders(xlEdgeLeft).LineStyle = xlContinuous


The code below is equivilant to the code above:

Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeLeft).LineStyle = xlContinuous


Result:

Top edge (xlEdgeTop):

The code below will create a simple border for the top edge of the range “B2:D4”:

Range("B2:D4").Borders(xlEdgeTop).LineStyle = xlContinuous


The code below is equivilant to the code above:

Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeTop).LineStyle = xlContinuous

Result:

Bottom edge (xlEdgeBottom):

The code below will create a simple border for the bottom edge of the range “B2:D4”:

Range("B2:D4").Borders(xlEdgeBottom).LineStyle = xlContinuous


The code below is equivilant to the code above:

Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeBottom).LineStyle = xlContinuous

Result:

Right edge (xlEdgeRight):

The code below will create a simple border for the bottom edge of the range “B2:D4”:

Range("B2:D4").Borders(xlEdgeRight).LineStyle = xlContinuous


The code below is equivilant to the code above:

Range(cells(2, 2), cells(4, 4)).Borders(xlEdgeRight).LineStyle = xlContinuous

Result:

Inside Vertical (xlInsideVertical):

The code below will create a simple border for the inside vertical edge of the range “B2:D4”:

Range("B2:D4").Borders(xlInsideVertical).LineStyle = xlContinuous

The code below is equivilant to the code above:

Range(cells(2, 2), cells(4, 4)).Borders(xlInsideVertical).LineStyle = xlContinuous

Result:

Diagonal Down (xlDiagonalDown):

The code below will create a simple diagonal down border for the range “B2:D4”:

Range("B2:D4").Borders(xlDiagonalDown).LineStyle = xlContinuous


The code below is equivilant to the code above:

Range(cells(2, 2), cells(4, 4)).Borders(xlDiagonalDown).LineStyle = xlContinuous


Result:

Diagonal Up (xlDiagonalUp):

The code below will create a simple diagonal up border for the range “B2:D4”:

Range("B2:D4").Borders(xlDiagonalUp).LineStyle = xlContinuous


The code below is equivilant to the code above:

Range(cells(2, 2), cells(4, 4)).Borders(xlDiagonalUp).LineStyle = xlContinuous

Result:

Complete Example:

In this section I’ve provided a complete example using borders in VBA for Excel. The user selects a border style from the drop down list in cell B1. Upon selecting a new value from the drop down list the borders for the range “G7:L15” will be adjusted accordingly:

Result:

The drop down list is created using data validation. For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists.

The code used in this example can be seen below:

Private Sub worksheet_change(ByVal target As Range)
'removes the current border in the range
Range("G7:L15").Borders.LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalDown).LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalUp).LineStyle = xlNone

'checks if the left edge border was selected
If Cells(1, 2) = "Left Edge" Then
    Range("G7:L15").Borders(xlEdgeLeft).LineStyle = _
    xlContinuous
'checks if the top edge border was selected
ElseIf Cells(1, 2) = "Top Edge" Then
    Range("G7:L15").Borders(xlEdgeTop).LineStyle = _
    xlContinuous
'checks if the bottom edge border was selected
ElseIf Cells(1, 2) = "Bottom Edge" Then
    Range("G7:L15").Borders(xlEdgeBottom).LineStyle = _
    xlContinuous
'checks if the right edge border was selected
ElseIf Cells(1, 2) = "Right Edge" Then
    Range("G7:L15").Borders(xlEdgeRight).LineStyle = _
    xlContinuous
'checks if the inside vertical border was selcted
ElseIf Cells(1, 2) = "Inside Vertical" Then
    Range("G7:L15").Borders(xlInsideVertical).LineStyle = _
    xlContinuous
'checks if the inside horizontal border was selected
ElseIf Cells(1, 2) = "Inside Horizontal" Then
    Range("G7:L15").Borders(xlInsideHorizontal).LineStyle = _
    xlContinuous
'checks if the diagonal down border was selected
ElseIf Cells(1, 2) = "Diagonal Down" Then
    Range("G7:L15").Borders(xlDiagonalDown).LineStyle = _
    xlContinuous
'checks if the diagonal up border was selected
ElseIf Cells(1, 2) = "Diagonal Up" Then
    Range("G7:L15").Borders(xlDiagonalUp).LineStyle = _
    xlContinuous
End If
End Sub


The main function for this program is a worksheet_change event handler. The event handler executes when ever the user selected a new value from the drop down list:

Private Sub worksheet_change(ByVal target As Range)
...
End Sub


The lines below remove any previous borders from the range “G7:L15”:

Range("G7:L15").Borders.LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalDown).LineStyle = xlNone
Range("G7:L15").Borders(xlDiagonalUp).LineStyle = xlNone


The If statement below checks if the selected value from the drop list is the left edge:

If Cells(1, 2) = "Left Edge" Then
    Range("G7:L15").Borders(xlEdgeLeft).LineStyle = _
    xlContinuous

If the If statement returns true a simple border for the left edge of range “G7:L15” is created.

You can download the file and code for this example here.

See also:

• Excel VBA, Format Cells and Ranges Using the Macro Recorder
• Excel VBA, Border Style and Thickness
• Excel VBA Border Colors
• Excel VBA, Remove Border
• Excel VBA, Get Border Properties
• Excel VBA Borders

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com