Day-to-day tasks, you may encounter the problem of bordering cells in Excel with VBA. You can do that quite easily using Excel VBA Borders LineStyle, as I have done in the earlier video, where I have inserted borders to all cells with the range B4:B12.
Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. To assist users in creating and manipulating data, it provides a wide range of features and operations. The ability to format cells and cell ranges is one of Excel’s useful features. Cell borders are a formatting feature in Excel that can be used to visually divide data and make it simpler to read. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences.
Excel comes with a programming language called VBA. Users can automate processes, write original functions, and change data thanks to it. One of the formatting options that may be accessed and changed using Excel VBA is Borders Linestyle. Users can customize the borderline’s appearance in a cell or range of cells by using the Borders Linestyle attribute. In this article, I will show you various properties and methods of Borders LineStyle in Excel with the help of VBA. Hence, read through the article to learn more and save time.
Getting Started with VBA in Excel
A macro is a sequence of instructions. This sequence of instructions automates some aspects of Excel that you do regularly. When you’re automating some of your work in Excel using macros, you can perform more efficiently and with fewer errors. For example, you may create a macro to format and print your month-end sales report. After the macro is developed, you can then execute the macro to perform many time-consuming procedures automatically.
A user doesn’t have to be a power user to build and use easy VBA macros. Simple users can simply turn on Excel’s macro recorder: Excel will document your activities and convert them into a VBA macro. When you execute this macro, Excel performs the actions again.
1. Enabling Developer Tab in Excel
All of the Office applications have a Developer tab in Excel Ribbon. You may find it hidden usually. You need to enable it through the settings option. We are more focused on the usage and application of Microsoft Excel here. But it is almost the same for any other Office application. However, when you’re planning to work with VBA macros, you’ve to make sure that the Developer tab is present on the Excel Ribbon.
2. Launching VBA Editor
Once you have the Developer tab, you will have access to the VBA editor window that will enable you to add, edit, or remove VBA code. Individual VBA codes are generally stored in a place called a module. It is usually a good practice to store different codes in different modules. Here is how you can create a module in the VBA window.
- Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
- Secondly, click the Insert button and select Module from the menu.
- Thirdly, it will create a module. However, it will look like the one below.
What Is Cell Border in Excel?
Usually, a cell border is a line that surrounds a cell or a group of cells. To make your border more attractive and to highlight it, you can also thicken or thin it. However, Excel provides us with features to make several changes in cell borders, and it can draw attention to the users for a particular segment in the worksheet. However, cell borders have several types of linestyles and you can add them manually or using VBA in Excel.
Overview of Borders.LineStyle Property in Excel VBA
The Borders Linestyle property contains multiple different options to choose from. These include solid lines, dotted lines, dashed lines, and many more. To set the Borders Linestyle property with the help of VBA, users must first select the range of cells they wish to format. This can be done using the Range object. It is a VBA object that represents a range of cells in Excel. After the range of cells is selected, users can utilize the Borders property to access the cell borders.
To set the Borders Linestyle property for all cells in a range in VBA, users can use the following syntax:
Range("A1:A5").Borders.LineStyle = xlContinuousDifferent Types Cell Borders
Excel offers several different border styles that can be applied to individual cells or ranges of cells. These styles include solid lines, dotted lines, dashed lines, combinations of dashed and dotted lines, double lines, and more. However, the syntax remains almost the same for this kind of variation. Let’s take a look at the syntax of various cell border types. Here, I have used a range of A1:A5 in order to add borders. However, you can change the range according to your needs.
Range("A1:A5").Borders.LineStyle = xlDash
Range("A1:A5").Borders.LineStyle = xlDashDot
Range("A1:A5").Borders.LineStyle = xlDashDotDot
Range("A1:A5").Borders.LineStyle = xlDot
Range("A1:A5").Borders.LineStyle = xlDouble
Range("A1:A5").Borders.LineStyle = xlSlantDashDotList of Borders Weight in Excel VBA
The Borders property is a collection of border objects, which represent the horizontal, vertical, top, bottom, left, and right borders of the selected range. Moreover, it includes diagonal borders inside a particular cell range. Each border object has a Linestyle property, which can be set to one of the available options.
Additionally, the border linestyle includes the border weight property. However, users can set 4 different types of border thicknesses, such as Hair line, Thin, Medium, and Thick. A combination of these properties can be used at a time, and the syntax may appear as below.
Range("A1:A5").Borders(xlEdgeLeft).Weight = xlHairline
Range("A1:A5").Borders(xlEdgeRight).Weight = xlHairline
Range("A1:A5").Borders(xlEdgeTop).Weight = xlThin
Range("A1:A5").Borders(xlEdgeBottom).Weight = xlThin
Range("A1:A5").Borders(xlDiagonalUp).Weight = xlMedium
Range("A1:A5").Borders(xlDiagonalDown).Weight = xlMedium
Range("A1:A5").Borders(xlInsideHorizontal).Weight = xlThick
Range("A1:A5").Borders(xlInsideVertical).Weight = xlThickExcel VBA Borders LineStyle Property (3 Easy Ways)
In this tutorial, I will show you how to utilize the linestyle property of borders in Excel VBA. But before jumping into that, I am going to show you different ways to insert borders in Excel. For the purpose of demonstration, I have used the following sample dataset. Here, the dataset contains the “Gradesheet of Students with Marks”. However, it contains the student ID, Name, Subject name, Marks, and Grade.
1. Applying Borders to All Cells with Text
Using borders is necessary when you have a plan to print your worksheet as they increase the reading capability and make your printed worksheet look more professional. You have to select the cells or ranges before using borders.
In this part, I will show you the process of applying borders to all cells with text with the help of Excel VBA. So, let’s get started to solve it.
- In the beginning, create a new module in the VBA window.
- Then, insert the following code in the module.
'Add Border to All Cells
'Declaring Sub-Procedure
Sub Add_Border_to_All_Cells()
'Declaring variables
Dim BrRange As Range
Dim BrCells As Range
'Provide the Range where you want borders
Set BrRange = Range("B4:F12")
For Each BrCells In BrRange
If Not IsEmpty(BrCells) Then
BrCells.BorderAround _
LineStyle:=xlContinuous, _
Weight:=xlThin
End If
Next BrCells
End SubIn this code, I have used the If loop to check whether the cells are empty or not. If the cells are not empty, the code will assign a thin (Weight:=xlThin) border around each cell. Now, the For loop checks each cell with the condition and assigns borders within the given Range.
- Finally, press the F5 key or the Run button to run the code.
2. Adding Borders in a Selected Range of Cells
Because of the dense data and complicated structure, Excel worksheets can be difficult to read at times. Inserting the border throughout a dataset can help us differentiate between parts and focus on specific data. Moreover, it makes the worksheet more representative. However, you can add borders to each cell in a particular range.
- First, create a new module in the VBA window.
- Then, insert the following code in the module.
'Add Border to Each Cell in Range
'Declaring Sub-Procedure
Sub Add_Border_to_Each_Cell()
'Declaring variables
Dim BrRange As Range
Dim BrCells As Range
'Provide the Range where you want borders
Set BrRange = Range("D4:F12")
For Each BrCells In BrRange
BrCells.BorderAround _
LineStyle:=xlContinuous, _
Weight:=xlThin
Next BrCells
End SubIn this code, I have used a For loop to apply the BorderAround method to each cell within the provided Range.
- Lastly, press the F5 key or the Run button to run the code and the output will appear as below.
3. Utilizing VBA BorderAround LineStyle Property to Add Thick Border
The BorderAround method is a VBA method that can be used to add a border to a cell or range of cells in Excel. With this method, you can only add borders around your specified range. However, it will not insert borders to individual cells. This method allows you to specify the style, weight, and color of the border that you want to apply. Usually, Excel offers a variety of border styles and options, but manually applying borders to each cell or range of cells can be a tedious and time-consuming task. However, with Excel VBA, you can automate the process of adding borders to your data. So, let’s get started.
- Initially, create a new module in the VBA window.
- Then, insert the following code in the module.
'Using BorderAround Method
'Declaring Sub-procedure
Sub BorderAround_Method()
'Select the Range where you want to add border
Range("B4:F12").BorderAround LineStyle:=xlContinuous, Weight:=xlThick
End SubIn this code, I have used the BorderAround linestyle property and inserted the weight of the line as Thick (xlThick). Here, I have selected the range as B4:F12.
- Finally, press the F5 key or the Run button to run the code.
How to Use Excel VBA Borders Linestyle Property: 4 Suitable Examples
As you know how to add borders to your dataset you can find it worth using different LineStyle properties in different circumstances. Let’s explore them.
1. Changing Colors in Borders
In Microsoft, Excel cells are distinguished by cell borders. But sometimes border colors are not visible or it appears as black by default. In that case, we need to change the border color. However, you can use any color for borders. Hence, go through the points mentioned below in order to use different colors in cell borders with the help of VBA.
- Firstly, create a new module in the VBA window.
- Secondly, insert the following code in the module.
'Change the Color of the Borders
'Declaring Sub-procedure
Sub Color_Borders()
'Declaring Variables
Dim myRange As Range
'Select the Range where you want to add border
Set myRange = Range("B4:F12")
With myRange.Border
.LineStyle = XlLineStyle.xlContinuous
.Weight = xlThin
'Choose your desired color
.Color = RGB(195, 10, 20)
End With
End SubIn this code, I have set the weight of the border as Thin and inserted the border color in RGB. You can input any color code according to your need.
- Thirdly, press the F5 key or the Run button to run the code and the output will appear as below.
2. Adding Borders Inside Cells with Excel VBA
Borders are used in Excel to make the report more attractive to users. While working in Microsoft Excel sometimes we need to add cell borders inside and outside. Cell borders usually separate data and help the user easily access specific data. In this section, I will show you how to add borders inside cells with VBA within a specific range provided by the user. For this purpose, I have added borders to all cells using the code from method-2. Here, I have chosen the Grade column to add inside borders in the cells.
- Initially, create a new module in the VBA window.
- Then, insert the following code in the module.
'Adding Borders Inside Cells
'Declaring Sub-procedure
Sub Insert_Border_Inside_Cells()
'Declaring Variables
Dim myRange As Range
'Select the Range where you want to add border
Set myRange = Range("F5:F12")
With myRange.Borders(xlDiagonalUp)
'Choose desired lineStyle
.LineStyle = XlLineStyle.xlDashDotDot
.Weight = xlThin
'Choose your desired color
.Color = RGB(195, 10, 20)
End With
With myRange.Borders(xlDiagonalDown)
.LineStyle = XlLineStyle.xlDashDotDot
.Weight = xlThin
.Color = RGB(195, 10, 20)
End With
End SubIn this code, I have chosen DashDotDot as the border linestyle. Here, I have added borders inside cells of the provided Range. For this reason, I have selected DiagonalUp and DiagonalDown as borders. It will add borders diagonally up and down inside a cell. Additionally, I have added color with the help of RGB. If you don’t insert this property, the color will appear as Black which is the default color.
- Finally, press the F5 key or the Run button to run the code and you will get your desired output.
3. Using VBA to Add Double Line Top Border in Excel
Borders have several line styles which you can use according to your preference. One of them is that you can apply a double line on the top border of each row in your dataset. However, you can apply it throughout the dataset. But here, I will show you how to use VBA in order to add a double-line top border for the purpose of demonstration.
- Initially, create a new module in the VBA window.
- Then, insert the following code in the module.
'Adding Double Line Top Border
'Declaring Sub-procedure
Sub Add_Double_Line_Top_Border()
'Declaring Variables
Dim myRange As Range
'Select the Range where you want to add border
Set myRange = Range("B4:F12")
'Choose LineStyle as Double and other parameters
With myRange.Borders(xlInsideHorizontal)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 10
End With
End SubIn this code, I have added a Double line top border in each row for the set Range. For this purpose, I have selected InsideHorizontal and Double as the linestyle. Moreover, I have added a Thick border with a ColorIndex of 10. You can choose a different one according to your choice.
- After that, press the F5 key or the Run button to run the code and you will receive your desired result.
Read More: How to Use Excel VBA Border ColorIndex Property
4. VBA to Change Border Line Thickness
In Microsoft Excel, there is a thin line border that is set as a default. To make this line more prominent, you may need to thicken the borderline. So, border thickness means inserting a deep line and removing the default borderline. There are several ways to make the borderline thick. I have tried to cover almost all of them. So, read through the rest of the part to learn more.
- First of all, create a new module in the VBA window.
- Then, insert the following code in the module.
'Changing Border Line Thickness
'Declaring Sub-procedure
Sub Change_Border_Line_Thickness()
'Select the Range where you want to add border and your desired thickness
Range("B4:F4").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
Range("B5:F12").BorderAround LineStyle:=xlContinuous, Weight:=xlThin
Range("E4:E12").BorderAround LineStyle:=xlContinuous, Weight:=xlThick
End SubIn this code, I have selected 3 different types of border weights for different parts of my dataset. You can change the Range according to your dataset.
- Next, press the F5 key or the Run button to run the code and you will receive your desired result.
Read More: How to Use Excel VBA Border Weight Property
How to Remove Borders with Excel VBA
Excel offers a couple of ways to remove cell borders. First of all, to remove an existing box border, you have to add borders first, which are very easy to make. After creating the borders, you can follow the steps to remove them. Those are very handy to use.
For the purpose of demonstration, I have added borders to the existing data using a previous method described above. Now, I will show a VBA code in order to remove the existing borders. So, stay connected and read through the rest of the part.
- Initially, create a new module in the VBA window.
- Then, insert the following code in the module.
'Remove Borders
'Declaring Sub-Procedure
Sub Remove_Borders()
'Declaring variables
Dim BrRange As Range
Dim BrCells As Range
'Provide the Range from where you want to remove borders
Set BrRange = Range("B4:F12")
For Each BrCells In BrRange
If Not IsEmpty(BrCells) Then
BrCells.Borders.LineStyle = xlNone
End If
Next BrCells
End SubIn this code, I have used a For loop to remove the borders from cells. The loop will check if the cells are empty or not. Here, if the cells are not empty then the borders will be removed. For this purpose, I have used None as the Border LineStyle property. This property will remove borders.
- Lastly, hit the Run button to run the code and your borders will be removed.
How to Create Variation in Border Colors with VBA in Excel
You can change the default color of your border with the help of Excel VBA. There are several properties of border color by which you can add colors to your borders. It includes RGB color value, ColorIndex number, and some direct color constants such as vbRed, vbBlue, vbBlack, vbWhite, vbMagenta, vbYellow, vbCyan, and vbGreen. In the below part, I will show you the syntax of some borders with these color properties so that you can use all of them according to your need. Here, I have used a range of B4:F12. However, you can change the range according to your needs.
Range("B4:F12").Borders().Color = vbRed
Range("B4:F12").Borders().Color = RGB(255, 0, 0)
Range("B4:F12").Borders().ColorIndex = 3These 3 syntax returns the same output. However, all of them will change the border color to Red. I have used 3 different color properties of borders in order to change the default color of borders.
Things to Remember
- You can change the Range in each code to change the location where you want to add borders linestyle.
- You can combine several linestyle properties at once with the help of Excel VBA.
- Moreover, you can remove all borders by using the Borders command from the Fonts group.
- Unlike gridlines, All Borders are always visible in printing.
- If you use the BorderAround method, it will only add borders around your given Range.
- However, you can change the code according to your preference.
Download Practice Workbook
Conclusion
These are all the steps you can follow to use VBA Borders LineStyle in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Here, I have demonstrated the cell borders and their various linestyle properties. Moreover, you can easily start your journey with VBA and apply different border styles with the help of VBA. Additionally, I have shown how to remove borders and change the border thickness and color combinations. Hopefully, you can now easily make the needed adjustments and use the codes according to your needs. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
Get FREE Advanced Excel Exercises with Solutions!
