How to Use Excel VBA Border Weight Property (8 Examples)

Get FREE Advanced Excel Exercises with Solutions!

When you have a large dataset and want to change the border thickness or even when you want to put a border within your dataset, you can use the Excel VBA border weight property.

Basically, you may think that it will be easier to insert borders with the help of Top Ribbon but when you have a large dataset and you want to highlight a particular cell or increase the border weight of that cell then you can do it with just one click by VBA code.

As you can see, you can choose an input range and according to a condition the thick border is applied to certain cells. Basically, if the final mark is below 50% then the thick border is applied to them. So, with this Borders.Weight property, you can highlight certain cells easily.


How to Launch VBA Editor in Excel

To write a VBA code in Excel, you have to launch the VBA editor. For launching this VBA editor, you must need the Developer tab, now if you don’t see the Developer tab then you have to enable the Developer tab.

  • To launch VBA Editor, you have to choose the Developer tab >> then select Visual Basic.

Opening of Visual Basic

  • Now, from the Insert tab >> you have to select Module. After inserting the Module, you need to write the preferable code as your dataset.

Insert Modul where you Write Codes

Note: You must save the Excel file as Excel Macro-Enabled Workbook (*xlsm) to run the code.


How to Add Borders Using VBA in Excel

Here, I am adding a default border for the range (“B4:H14”).

The dataset contains some students’ results. I am using this dataset as border weight will be useful while printing any data. And the student’s result is a printable thing.

Excel VBA Code to add border weight

Sub Giving_Border()
Range("B4:H14").Borders.ColorIndex = xlColorIndexAutomatic
End Sub

This code will insert borders within the mentioned range.

Lastly, to run the code, from Macros >> select the Sub-Procedure named Giving_Border and press Run. The output is given below.


Overview of Borders.Weight Property in Excel VBA

In Borders.Weight property you can use four expressions. They are xlThin which denotes a thin border; xlMedium which will increase border weight at a medium level; xlThick which gives the highest thickness; and xlHairline which returns the thinnest border.

In the video, I used xlMedium so the ranges are introduced to a medium-level thickened border.

This Borders.Weight property can not only change the thickness of the border but also can add borders to none bordered cells that I have shown in the above video.

VBA Code for Border Weight Property

Sub Syntax_Border_Weight()
'You can use Borders Property with this Range
'You must write the Range
Range("B4:D14").Borders.Weight = xlMedium
'Or you can use Borders Property with Worksheet
Worksheets("Border Weight Syntax").Range("E4:H14").Borders.Weight = xlMedium
End Sub

Code Breakdown

This is a simple code for using Borders.Weight property along with the Range object.

Range("B4:D14").Borders.Weight = xlMedium

With this, you can apply borders to the B4:D14 range or if there is any border then this code will increase the thickness of that border to a medium level.

Worksheets("Border Weight Syntax").Range("E4:H14").Borders.Weight = xlMedium

This particular portion of code will call the worksheet named Border Weight Syntax and then change the border thickness or add borders to the E4:H14 range.

  • To run the code, from Macros >> select the Sub-Procedure named Syntax_Border_Weight and press Run. Finally, you will see the following output.

Furthermore, you can use numeric values instead of using xlMedium.

Below, I have given the chart for using numeric values.

Range("B4:D14").Borders.Weight = 1

denotes Hairline Border.

Range("B4:D14").Borders.Weight = 2

creates Thin Border.

Range("B4:D14").Borders.Weight = 3

makes Medium Border.

Range("B4:D14").Borders.Weight = 4

represents Thick Border.


How to Use Excel VBA Border Weight Property: 8 Examples

Now I will explain 8 easy examples to show you how you can use Excel VBA border weight. So, let’s see the examples below.


1. Applying Border Weight Property for Active Cell

Here you can see an interesting thing. Basically, I have attached a button to the worksheet. So, when you press the button the code will run. The code describes when you select a cell that will activate so the thick border will be applied to that selected cell.

Thus I have introduced the button. Where you need a thick border to highlight that cell value, you can just select that and press the button.

Code for Implement Borders on Active Cell only

Sub Border_for_ActiceCell() 
  ActiveCell.Borders.Weight = xlThick 
End Sub

This code says a thick border will be applicable for the active cell.

Now, follow the steps for inserting the button.

  • First, write the above code in the Module.

Inserting Button to Run VBA Code in Excel

  • Then, from the Developer tab >> go to Insert >> from Form Controls >> choose Button (Form Control) >> drag the Button to a suitable place on the worksheet.

Assigning Macro for the Button to Run Code

  • Now, you will get a pop-up box named Assign Macro. Select that particular sub-procedure where you have written the code. As I have named the sub-procedure as Border_for_ActiveCell so I am going to choose that one >> then press OK.

That’s it. Now, you can press the button and you will see that the selected cell is bordered with the thick one.

Edit Name of the Button

This is not a mandatory part. If you want then you can change the name of the button. Sometimes you may use so many buttons, so with only a number or default name you may not understand which button returns what value. That’s why I have renamed Button 2 (default) to another name. To do so, right-click on Button 2 >> from the Context Menu Bar >> select Edit Text  >> then write down the preferable name. Here, I have named the button Apply Border.

Pressing on Button to show output

For example, I selected the H10 cell and then pressed Apply Border.

Result of Using Borders Weight in Excel VBA

As a result, the H10 cell is changed to a thick border. In this way, you can set the weight of a border with Excel VBA.


2. Use of For Each Loop to Change Border Weight of a Certain Range

Here, I have used a different property BorderAround to change the thickness of the border. Actually, this is another way to apply a border or to increase the border thickness. When you need to apply some conditions for borders, in that case, you should use For Each Next Loop. Here, I have shown a general way of using this border weight property within For Each Loop. Let’s see how to use For Each Loop to change the border weight of a specified range.

For Each Loop within BorderAround Property

  • Now, write the following code in the Module.
Sub borders_with_for_loop()
Dim my_Rng, my_Cell As Range
Set my_Rng = Range("B4:H14")
For Each my_Cell In my_Rng
my_Cell.BorderAround _
Weight:=xlMedium
Next my_Cell
End Sub

Code Breakdown

  • Here, I declare two variables my_Rng, my_Cell as Range.
  • Then, set the range applicable to the active worksheet.
  • I use a For Each Next loop to call each cell from the mentioned range and the border weight of called cell will be changed to a medium level of thickness.
  • Then, save the code >> press the Run button and you will see the following output.

This will increase the border thickness within range B4:H14 of the active worksheet.


3. Select a Range and Change Border Weight

When you need to change the border weight with Excel VBA for a selected range, or in case of changing the range again and again, or adding a new range and want to change the border weight, then this example will help you. Here, you should select the range in your worksheet and then run this code.

Use Selection Property with Borders in VBA

  • Write down the following code in a new Module.
Sub Border_on_Selecting_Cells()
Selection.Borders.Weight = xlThick
End Sub

This code says that the Thick border will apply to the selected range.

  • To run this code, select the range in the worksheet >> then press the Run button.

4. Inserting Border with Borders.Weight Property for a Numeric Condition

If you want an input range from the user then you should see this example. In this example, I will explain how to use a numeric condition to highlight some cells which will fulfill the conditions. Here, with this code, I will check who gets less than 50% of the final marks.

Code for Applying Numeric Condition with Borders

  • Now, write down the corresponding code in a new Module.
Sub Border_based_cell_numeric_Value()
Dim my_Rng As Range
Dim my_Cell As Range
Set my_Rng = Application.InputBox(Title:="Exceldemy", _
Prompt:="Select the Range for Borders", Type:=8)
For Each my_Cell In my_Rng
If my_Cell < 0.5 Then
my_Cell.BorderAround _
Weight:=xlThick
End If
Next my_Cell
End Sub

Code Breakdown

  • Here, I declare two variables my_Rng, and my_Cell as Range.
  • Then, set the range using an InputBox. Type:=8 for InputBox denotes that instead of typing range, the user can select a cell reference or range from the worksheet.
  • I use a For Each Next loop to call each cell from that selected range.
  • If-End If statement implies that the border weight of the called cell will be changed to maximum thickness if the cell value is less than 50%.
  • Then, save the code >> press the Run button and you will see the following output.

As you can see, I selected the range first (Final Mark column), then ran the code and so all the marks less than 50% got marked with a thick border.


5. Applying Border Weight When Cell Value Is Not Empty

If you have some empty cells within your dataset and you want to use the border only for the cells which are not empty then you can use this code.

Code to Border only Valued Cells

  • So, use the code given below in your Module.
Sub Border_when_cells_are_not_empty()
Dim my_Rng, my_Cell As Range
Set my_Rng = ThisWorkbook.ActiveSheet.Range("B4:G14")
For Each my_Cell In my_Rng
If Not IsEmpty(my_Cell) Then
my_Cell.BorderAround _
Weight:=xlThin
End If
Next my_Cell
End Sub

Code Breakdown

  • Here, I declare two variables my_Rng, my_Cell as Range.
  • Then, set the range using ThisWorkbook.ActiveSheet.Range property.
  • I use a For Each Next loop to call each cell from the given range.
  • If Not-End If statement implies that when the cell value is empty, the border weight of the cell will remain the same.
  • To run the code, go to Macros >> select the sub procedure named Border_when_cells_are_not_empty >> press Run.

You can see if there is an empty cell in the middle of some valued cells then that cell will be bordered automatically.


6. Increasing Border Weight If the Cell Contains Text

Here, I have used a different dataset. This dataset contains some employee information, now I want to mark the department named IT. So, this is an example of how you can change the border weight based on a cell having a specified string.

Code of How to Use String in Condition

  • Now, use the code in a new Module and check the result.
Sub Border_based_on_cell_text_Value()
Dim my_Rng As Range
Dim my_intgr As Integer
Set my_Rng = Selection
For my_intgr = 1 To my_Rng.Rows.Count
    If my_Rng.Cells(my_intgr).Value = "IT" Then
    my_Rng.Cells(my_intgr).Borders.Weight = xlThick
    End If
Next my_intgr
End Sub

Code Breakdown

  • Here, I declare two variables my_Rng as Range and my_intgr as Integer.
  • Then, set the range using the Selection property. So, the user must select the range and then run this code.
  • I use a For Next loop to call each cell from the given range.
  • If-End If statement says that when the cell value is IT, the border weight of the cell will be thickened.
  • To run the code, select the range (where the cells have department name as the condition is based on department name) >> then go to Macros >> select the sub procedure named Border_based_on_cell_text_Value >> press Run.

If you don’t select the correct column before running the code then this will not show any output.


7. Changing Border Weight If Cell Contains Date

Changing Border Weight Based on Cell Value as Date

Suppose you have a dataset having some dates, now you want to mark certain dates. In that case, you may see this example. Here, I will explain how you can highlight a date using Borders.Weight property.

Condition When the Cell Values are Date

  • Firstly, write down the code given below in a Module.
Sub Border_based_on_cell_Date_Value()
Dim my_Rng As Range
Dim my_intgr As Integer
Set my_Rng = Selection
For my_intgr = 1 To my_Rng.Rows.Count
If my_Rng.Cells(my_intgr).Value > DateSerial(2014, 1, 1) Then
my_Rng.Cells(my_intgr).Borders.Weight = xlMedium
End If
Next my_intgr
End Sub

Code Breakdown

  • Here, I declare two variables my_Rng as Range and my_intgr as Integer.
  • Then, set the range using the Selection property. So, a user must select the range and then run this code.
  • I use a For Next loop to call each cell from the given range.
  • DateSerial(2014, 1, 1) denotes the date of 1st January 2014. So, DateSerial takes arguments like (Year, Month, Day).
  • If-End If statement says that when the cell value is greater than 1st January of 2014, the border weight of the cell will change to a medium-level thickness.
  • At this time, select the range (where the cells have date as the condition is based on date) >> then go to Macros >> select the sub procedure named Border_based_on_cell_Date_Value >> press Run.

Introducing Borders on Certain Dates

Here, you can see that the hiring dates that are after 01-01-2014 get marked.


8. Changing Border Weight for One Side of Cell with Edge Property

In this video, you can see that there is a different border on the different sides of cells. So, if you need to mark a cell with varieties of border thicknesses then you can follow this example.

Code to Show How to Use Edge Property in Border

  • Write this code in a Module.
Sub Border_at_One_Side_Cell()
Set my_Rng = Range("B4:H14")
With my_Rng
.Borders(xlEdgeLeft).Weight = xlThick
.Borders(xlEdgeTop).Weight = xlThick
.Borders(xlEdgeRight).Weight = xlThick
.Borders(xlEdgeBottom).Weight = xlThick
.Borders(xlInsideHorizontal).Weight = xlMedium
.Borders(xlInsideVertical).Weight = xlThin
End With
With Range("H13:H14")
.Borders(xlDiagonalDown).Weight = xlHairline
.Borders(xlDiagonalUp).Weight = xlHairline
End With
End Sub

Code Breakdown

  • Here, I use a variable named my_Rng as Range.
  • Then, set the range B4:H14 of the active worksheet.
  • With-End With statement implies that all the properties, inside this statement, will be applicable for the mentioned range (my_Rng). So, you don’t need to use Range. property repeatedly.
  • Similarly, the 2nd With-End With statement will work for range H13:H14.
  • When you want to border one side of a cell then you should use this Borders(xlEdge…) property. Basically, you should specify the side beside the Borders property.
  • Then, go to Macros >> select the sub procedure named Border_at_One_Side_Cell >> press Run.

As a result, you will show this output.


How to Use Borders.Value Property in Excel VBA

How to Use Borders.Value Property

In this section, I will explain how to use Borders.Value property. With the help of this Borders.Value property you can change the line style or include a new line style. In the Borders.Value property you can use from 0 to 13 numeric numbers.

Borders.Value = 0

denotes there will be no border. Then

Borders.Value = 1

denotes a thin continuous border.

In the same way, you can use up to Borders.Value = 13.

Use Borders.Value Property in VBA Code in Excel

  • As an example, I have used some numbers for your better understanding. So, write the following code in a new Module and check the result.
Sub Border_Value()
Range("D4:G14").Borders.Value = 2
Range("H4:H12").Borders.Value = 4
Range("D13:H14").Borders.Value = 3
Range("B4:C14,D4:H5").Borders.Value = 1
End Sub

Code breakdown

Range("D4:G14").Borders.Value = 2

It will create a dashed border for D4 to G14 cells.

Then,

Range("H4:H12").Borders.Value = 4

It will make the dash dot-dot type line style for H4 to H12 cells.

Range("D13:H14").Borders.Value = 3

It will change the border to a dotted border for D13 to H14 cells.

Finally,

Range("B4:C14,D4:H5").Borders.Value = 1

This will make a thin continuous border for both the range B4 to C14 and D4 to H5.

  • Now, go to save the code >> from Macros >> choose the sub-procedure named Border_Value >> press Run.
  • Then, you will get the following result.

Result Based on Borders.Value Property


How to Color Borders in Excel VBA

How to Color Borders in Excel

Suppose you need to make a dataset eye-catchy by using a colorful border. Or, say, you want to highlight some cells by changing the border colors. Then this section will be helpful for you.

The most interesting thing is that you can use colors on your border with the VBA code. You can use lots of shades with different color codes.

There are 3 ways to make color with VBA code for the border. One is, you can use RGB (decimal) color coding. In this case, you have to create a color combination with 3 basic colors which are red, green, and blue. So, you must give 3 arguments as RGB(red, green, blue). Here, you can insert from 0 to 255 for any color. 0 and 255 denote respectively the lowest and highest shade of color. So, RGB(0,0,0) will give black color and RGB(255, 255, 255) will give white color.

If you give RGB(0,0,255) then you will see blue color. Now, you can make your own color by changing the values of basic colors. You should keep the ratio according to your wanted shade. Like, if I want to create a shade of red color, then I may use RGB(215, 50,70). This will give a maroon-type shade.

For coloring the border you have to use this with Borders.Color property.

Then, you can use the color name directly but in this case, you have to add “vb” before the name of the color. Like, Borders.Color = vbYellow.

The last one is, you can use the ColorIndex property.  Here you can insert 1 to 56 as the ColorIndex value.

Use Colors in VBA Code

  • Here is the code where I showed different ways how to color the borders. You may use this code in the Module and check what it gives.
Sub Coloring_on_Border()
Range("B4:H14").Borders.Color = RGB(50, 100, 255)
Range("H6:H12").Borders.Color = RGB(200, 50, 100)
Range("E5:E12").Borders.Color = vbGreen
Range("G5:G12").Borders.Color = vbGreen
Range("D6:D12").Borders.ColorIndex = 21
Range("F6:F12").Borders.ColorIndex = 21
End Sub
  • Lastly, press Run and show the output in the Excel sheet.

Run the Code to Show Used Colors

Read More: How to Use Excel VBA Border ColorIndex Property


How to Use Different Borders with LineStyle Property in Excel VBA

How to Use Different VBA Borders with Line Styles

You can change the line style of the border too. Sometimes you may have different types of data within a single sheet and you may need to change the line style of the border for identifying different datasets. So, let’s see how you can change the borderline style with the VBA code.

Code for Different Line Styles

  • From the Developer tab >> go to Visual Basic >> insert a Module >> write the corresponding code in the Module.
Sub Changing_Line_Style()
Range("B4:H14").Borders.LineStyle = xlContinuous
With Range("B6:H12")
.Borders(xlInsideHorizontal).LineStyle = xlDot
.Borders(xlEdgeBottom).LineStyle = xlSlantDashDot
End With
Range("C6:H12").Borders(xlInsideVertical).LineStyle = xlDouble
Range("D6:E12,F6:G12").Borders(xlInsideVertical).LineStyle = xlDash
Range("B13:G14").Borders(xlInsideHorizontal).LineStyle = xlDashDotDot
End Sub

Code Breakdown

  • Here, I make the border continuous for the range B4 to H14.
  • Then, use With-End With statement to make the horizontal line of cells of mentioned range, dotted and bottom line of the range, slant-dash-dot type.
  • Use a double line for vertical segments of cells of range C6 to H12. And, make dash-type line for vertical segments of cells of both the range D6:E12 and F6:G12.
  • Then, change the line style of range B13:G14 to make the horizontal segments dash-dot-dot.
  • When you want to change the line style of the border for one side of a cell then you should use this Borders(xlEdge…) property. Basically, you should specify the side beside the Borders property.
  • Then, go to Macros >> select the sub-procedure named Changing_Line_Style >> press Run and you will see the output.

Run the Code to See the Line Styles


How to Apply Different Border Property in Excel VBA

How to Apply Different VBA Border Property

When you want to implement different borders property within a single code for decorating your dataset, you can follow this section.

Different Borders Property within one Code

  • Use the following code in a Module.
Sub Different_Border_Properties()
With Worksheets("Different Borders").Range("B4:H14")
.Borders.LineStyle = xlSlantDashDot
.Borders(xlInsideHorizontal).Weight = xlThin
.Borders(xlInsideVertical).Color = vbRed
End With
End Sub

Code Breakdown

  • Here, I fixed the worksheet and range by With-End With statement. So, this code will work only for the range B4:H14 of the worksheet named Different Borders.
  • Then, made the line style of borders slant-dash-dot type.
  • Decreased the horizontal segment of cells to thin borders.
  • Changed the colors of the vertical segment to red.
  • Finally, press the F5 key to run this code and see the output.

Result of Different Borders Type


How to Remove All Borders in Excel with VBA

If you need to remove the used borders then of a particular range then you can see this section. Here, I will describe the code by which you can remove all borders.

In the section Use of Borders.Value property, I have already mentioned a way to remove the border. According to that, you can use Borders.Value= 0 for deleting the borders.

Else you can use

.Borders.ColorIndex = xlColorIndexNone

 or,

.Borders.LineStyle = xlNone.

VBA Code to Remove all Borders in Excel

  • Write down the code given below in a Module.
Sub Border_based_on_cell_Date_Value()
Dim my_Rng As Range
Dim my_intgr As Integer
Set my_Rng = Selection
For my_intgr = 1 To my_Rng.Rows.Count
    If my_Rng.Cells(my_intgr).Value > DateSerial(2014, 1, 1) Then
    my_Rng.Cells(my_intgr).Borders.Weight = xlMedium
    End If
Next my_intgr
End Sub

Code Breakdown

Range("B4:D14").Borders.ColorIndex = xlColorIndexNone

 → this will remove the border of range B4:D14.

Range("E4:H14").Borders.LineStyle = xlNone

this will remove the border of range E4:H14.

Actually, for your better understanding, I showed those two ways for two different ranges. You can use any of them for the whole range.

  • Now, from the Macros >> choose Removing_Border >> press Run.

Frequently Asked Questions

  • Where Can I Find the Developer Tab in Excel?

You can find the Developer tab in Excel Ribbon. If you don’t see the Developer tab then you have to enable the Developer tab. Follow the link and you will be able to enable the Developer tab.

  • How Can I Change Line Thickness in Excel?

Here, you can change the Borders.Weight value to change the line’s thickness. You can check the Borders.Weight property, described earlier, for a detailed explanation.


💬 Things to Remember

If you keep the name of a sub-procedure that is similar to the name of any used property then the code will not work. So, be careful about the naming of sub-procedures.


Practice Section

Practice Section to Use Border Weight with VBA in Excel

Now you can practice by yourself and learn how to use the Excel VBA border weight property.


Download Practice Workbook


Conclusion

In this article, I have explained the topic of Excel VBA Border Weight property but also I have tried to cover all the topics for how you can modify your borders on the dataset. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo