Excel VBA Borders

One of the most common things you will be working with in Excel are borders. In this article I’ve have briefly explained the different customizations you can apply to borders using VBA for Excel. I’ve also provided links to more detailed explanations for each section.

Jump To:

 


Contents

Creating Borders:

The code below will create a simple border on the bottom edge of the cell B2:

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

Result:

Excel VBA, Border Create

Using VBA you can choose to create borders for the different edges of a range of cells:

  • Left edge
  • Right edge
  • Bottom Edge
  • Top Edge
  • Inside Edges
  • Diagonals

This topic has been covered in detail the article below:


Remove Border:

Removing borders is similar to creating borders. You need to decide which edge you want to remove the border from and assign the  xlNone line style property to that edge.  For example the code below removes the border from the bottom edge of cell A1:

Range("A1").Borders(xlEdgeBottom).LineStyle = xlNone

I have explained this topic in detail in the article below:


Linestyle and Thickness:

Border linestyle and thickness can be changed using the .LineStyle and .Weight properties. In the example below a border with the “Dash Dot” line style is created for the right edge of cell B2:

Range("B2").Borders(xlEdgeRight).LineStyle = xlDashDot

Result:

Excel VBA, Line Style Dash Dot
The code below changes the thickness of the border created to “Medium”:

Range("B2").Borders(xlEdgeRight).Weight = xlMedium

Result:

Excel VBA, Line Weight Medium
I have covered this topic in detail in the article below:


Border Color:

Border colors can also be changed using VBA for Excel. Lets assume there is an all round border in cell B2:

Excel VBA, Border, Before Color
The code below changes the color of the border on the bottom edge to green. For more information about working with colors in VBA please see:

Range("B2").Borders(xlEdgeBottom).Color = vbGreen

Result:

Excel VBA, Border Color
I’ve covered this topic in detail in the article below:


Get Border Properties:

You can check if a border exists in a cell and even get the different properties of the border (Color, Style, Weight, … ) using VBA for Excel. For example the code below checks if a border exists in the bottom edge of cell B2:

Sub main()
If Range("B2").Borders(xlEdgeBottom).LineStyle <> _
xlNone Then
    'border exists: Your code here
Else
    'border doesn't exist: Your code here
End If
End Sub

The code below gets the line style used in the bottom edge of the border in cell B2 and stores it in the variable intStyle:

Dim intLineStyle As Long
intLineStyle = Range("B2").Borders(xlEdgeBottom).LineStyle

The code below gets the thickness/weight used in the bottom edge of the border in cell B2 and stores it in the variable intThickness:

Dim intThickness As Long
intThickness = Range("B2").Borders(xlEdgeBottom).Weight

The code below gets the color used in the bottom edge of the border in cell B2 and stores it in the variable intColor:

Dim intColor As Long
intColor = Range("B2").Borders(xlEdgeBottom).Color

I have covered this topic in detail in the article below:

See also:

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

Leave a Reply

Your email address will not be published. Required fields are marked *