Borders in VBA Excel
We use borders in excel to highlight our data, in any dashboard, it is very important skill to represent the important data and it is done by borders, borders are a property in VBA which can be accessed by using the range method and giving the appropriate border style as we know there are different border styles.
If you are good at formatting in excel, then you can call yourself an “Excel Beautician.” To make the reports looks appealing to the end-user or readers, formatting plays a vital role in it. I hope you are well aware of the formatting techniques in the regular worksheet. Formatting through VBA coding requires a considerable amount of VBA coding language. In this article, we will show you one of the most underrated formatting techniques i.e., applying excel VBA borders.
Examples of Borders in VBA
Let us take a look at some examples to understand this in a better manner.
Example #1 – Apply VBA Borders with Coding
Creating a macro to apply excel VBA borders with different styles, making them available as an add-on to the excel ribbon, makes the job easier whenever we want to apply VBA borders.
Every cell in a worksheet has borders and background colors. By default, every cell has no border and background color.
In the worksheet under the HOME tab we have, we have a border option, and if you click on the drop-down list in excel, we have a variety of options.
But in VBA first thing we need to do is to decide the cell or range of cells we are going to apply the excel VBA borders formatting styles. So we can refer to the range of cells or cells using VBA RANGE object. For example, if you want to change the border of the cell B5, then you can write the code like this.

4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
Range(“B5”)
Then we need to access the “Borders” property.
Code:
Sub Border_Example1() Range("B5").Bo End Sub
After applying the excel VBA “Borders” property, open parenthesis to see all the kinds of border formatting options.
Code:
Sub Border_Example1() Range("B5").Borders( End Sub
Here we have xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, and xlInsideVertical.
After the border style has been selected, we need to select the characteristic we want to work with. One of the characters we need to use here is “Line Style,” so select the “Line Style” property.
Code:
Sub Border_Example1() Range("B5").Borders (xlEdgeBottom).li End Sub
Once the line style property is selected, we need to set the line style type of line or type of borders in VBA we are going to apply.
Put equal sign and select “XlLineStyle” enumeration.
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle =XlLineStyle. End Sub
Put dot to see all the available border styles.
Code:
We have many options here. xlContinuous, xldash, xlDashDot, xlDashDotDot, xlDot, xlDouble, XlLineStyleNone, and xlSlantDashDot.
Ok, now I have selected the option of “xlDouble.”
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble End Sub
Now, if I run the code, it will apply the Double line to the bottom of the cell B5.
Line Type: “xlContinuous”.
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous End Sub
Result:
Line Type: “clash.”
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDash End Sub
Result:
Line Type: “xlDashDot.”
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDashDot End Sub
Result:
Line Type: “xlDashDotDot”.
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDashDotDot End Sub
Result:
Line Type: “xlDot.”
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDot End Sub
Result:
Line Type: “xlLineStyleNone”.
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlLineStyleNone End Sub
Result:
This will remove the border of the specified cell.
Line Type: “xlSlantDashDot”.
Code:
Sub Border_Example1() Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlSlantDashDot End Sub
Result:
Example #2 – Change the Border Using VBA Borders Around Method
We can also change the borders of the cell by using the VBA Borders Around method. Once the range of cells or cells is mentioned, we need to access the VBA Borders Around method.
Open parenthesis to see all the parameters.
Range(“B5”).BorderAround([Line Style], [Weight as xlBorderWeight], [ColorIndex], [Color], [Theme Color])
We can mention the line style, color of the line, border weight, and many more things we can do with this method.
Code:
Sub Border_Example1() Range("B5").BorderAround LineStyle:=xlContinuous, Weight:=xlThick End Sub
This will change the Line Style to xlContinuous.
LineStyle:=xlContinuous
The weight of the Border is thick.
Weight:=xlThick
And the result of this code is as below.
Like this using excel VBA Borders and Border Around property and method, we can change the border and elements of borders through VBA coding.
Recommended Articles
This has been a Guide to VBA Borders. Here we learn how to set Borders with Excel VBA Coding along with practical examples & downloadable code templates. You may also have a look at other articles related to Excel VBA –
- What are Global Variables in Excel VBA?
- Examples to Create a Pivot Table in VBA
- Excel VBA End
- Conditional Formatting for Blank Cells
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion