VBA Cell Borders
This tutorial will show how to adjust cell border settings in VBA.
Formatting Borders
Top Border – Double Line
First let’s look at an example of how to set a blue, thick, doubled top border to the cell B3 on Sheet1:
1 2 3 4 5 6 7 8 9 |
Sub ExampleSetBorder() With Worksheets("Sheet1").Range("B3").Borders(xlEdgeTop) .LineStyle = xlDouble .Weight = xlThick .ColorIndex = 5 End With End Sub |
Notice we use a With Statement to simplify our coding.
We access the the top border with .Borders(xlEdgeTop).
Other Cell Borders
Instead you can access other borders, including diagonals:
1 2 3 4 5 6 |
Worksheets("Sheet1").Range("B2").Borders(xlEdgeTop).Color = RGB(128, 0, 0) Worksheets("Sheet1").Range("B2").Borders(xlEdgeRight).Color = RGB(0, 255, 0) Worksheets("Sheet1").Range("B2").Borders(xlEdgeBottom).Color = RGB(0, 0, 128) Worksheets("Sheet1").Range("B2").Borders(xlEdgeLeft).Color = RGB(255, 0, 0) Worksheets("Sheet1").Range("B2").Borders(xlDiagonalDown).Color = RGB(0, 0, 0) Worksheets("Sheet1").Range("B2").Borders(xlDiagonalUp).Color = RGB(0, 0, 0) |
Inside Borders
You can also adjust horizontal (xlInsideHorizontal) and vertical (xlInsideVertical) borders for all cells in a range:
1 2 3 4 5 |
With Worksheets("Sheet1").Range("B2:C6").Borders(xlInsideHorizontal) .LineStyle = xlDashDotDot .Weight = xlMedium .ColorIndex = 3 End With |
LineStyle
The LineStyle property can be changed to xlContinuous, xlDot, xlDash, xlDashDot, xlDashDotDot, xlDouble, xlSlantDashDot and xlLineStyleNone.
The Weight property can be either xlHariline, xlThin, xlMedium and xlThick. You will have this result with the code below:
1 2 3 4 5 6 7 8 |
Worksheets("Sheet1").Range("C3").Borders(xlEdgeTop).Weight = xlHairline Worksheets("Sheet1").Range("C3").Borders(xlEdgeBottom).Weight = xlHairline Worksheets("Sheet1").Range("D3").Borders(xlEdgeTop).Weight = xlThin Worksheets("Sheet1").Range("D3").Borders(xlEdgeBottom).Weight = xlThin Worksheets("Sheet1").Range("E3").Borders(xlEdgeTop).Weight = xlMedium Worksheets("Sheet1").Range("E3").Borders(xlEdgeBottom).Weight = xlMedium Worksheets("Sheet1").Range("F3").Borders(xlEdgeTop).Weight = xlThick Worksheets("Sheet1").Range("F3").Borders(xlEdgeBottom).Weight = xlThick |
Border colors can be set as ColorIndex or Color (RGB, serial number or vbColor). See more information about color settings here.
You can add borders around a range with a one-line command as well:
1 |
Range("B5:C7").BorderAround LineStyle:=xlContinuous, Weight:=xlThick, Color:=vbRed |
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!