To apply borders to a cell using VBA in Excel, you can use two different ways. The first is the “Borders“ property and the second is the “BorderAround” method. The only difference between these two ways is in the “Borders” property you can even apply the border to one side of the cell and in the border around it applies to the cell completely.
In this tutorial, we will explore both ways and look at what are the different ways to add the border.
VBA Border Property
- First, you need to specify the range or the cell where you wish to apply the border using the range object.
- After that, type a dot (.) and then select the “Borders” property from the list of properties and methods.
- Next, specify the border index from the contants avaiable. Here I’m using “xlEdgeBottom” to apply border only to the bottom of the cell.
- From here, specify the line style using the “LineStyle”. I’m using the “xlContinuonus” as the line style.
Sub vba_borders() Range("A1") _ .Borders(xlEdgeBottom) _ .LineStyle = XlLineStyle.xlContinuous End Sub
Now when you run this code, it will add a border to the bottom of the cell A1.
Using Different Colors with Color Index/Color
Just like you normally choose the color for the border when you do it manually. In the border property, you can further use the color index and color property to use a color other than the default color.
Consider the below code.
With Range("A1").Borders(xlEdgeBottom) .LineStyle = XlLineStyle.xlContinuous .Color = RGB(255, 0, 0) End With
When you run this macro, it adds a border to the bottom of the cell with a red color.
Or if you want to use the colorindex property then the code would be like that.
With Range("A1").Borders(xlEdgeBottom) .LineStyle = XlLineStyle.xlContinuous .ColorIndex = 7 End With
Related: VBA With Statement
Add Border Inside a Cell
If you want to apply a border inside a cell, in that case, you need to use “xlDiagonalUp” and “xlDiagonalDown” as the xlBorderIndex.
With Range("A1").Borders(xlDiagonalUp) .LineStyle = XlLineStyle.xlDashDotDot .Color = RGB(255, 0, 0) End With With Range("A1").Borders(xlDiagonalDown) .LineStyle = XlLineStyle.xlDashDotDot .Color = RGB(255, 0, 0) End With
When you run the above code, it adds a border inside the cell upward and downward.
VBA BorderAround Method
In VBA’s BorderAround property, you have 5 arguments which are optional but two need to be specified to apply the border to a cell in the right way.
The following code applies the border around the range A1:A3 when you run the macro.
Range("A1:A3").BorderAround _ LineStyle:=xlContinuous, _ Weight:=xlThin
Add Border to Each Cell in the Range
The following code loops through all the cells in the range and applies the border to each cell one by one.
Sub vba_borders() Dim iRange As Range Dim iCells As Range Set iRange = Range("A1:A10") For Each iCells In iRange iCells.BorderAround _ LineStyle:=xlContinuous, _ Weight:=xlThin Next iCells End Sub
Apply Border to All the Cells with Text
The following code uses a FOR LOOP, USED RANGE, IFEMPTY, and IF STATEMENT to apply borders to the only cells where you have value.
Dim iRange As Range Dim iCells As Range Set iRange = ThisWorkbook.ActiveSheet.UsedRange For Each iCells In iRange If Not IsEmpty(iCells) Then iCells.BorderAround _ LineStyle:=xlContinuous, _ Weight:=xlThin End If Next iCells
You just need to use the line style “xlNone”.
Range("A1").Borders(xlDiagonalDown).LineStyle = xlNone
And if you want to remove the border from all the cells in a worksheet where you have a value, consider the following code.
Dim iRange As Range Dim iCells As Range Set iRange = ThisWorkbook.ActiveSheet.UsedRange For Each iCells In iRange If Not IsEmpty(iCells) Then iCells.Borders(xlDiagonalDown).LineStyle = xlNone End If Next iCells
- Count Rows using VBA in Excel
- Excel VBA Font (Color, Size, Type, and Bold)
- Excel VBA Hide and Unhide a Column or a Row
- Excel VBA Range – Working with Range and Cells
- Find Last Row, Column, and Cell using VBA in Excel
- Insert a Row using VBA in Excel
- Merge Cells in Excel using a VBA Code
- Select a Range/Cell using VBA in Excel
- SELECT ALL the Cells in a Worksheet using VBA
- ActiveCell in VBA in Excel
- Special Cells Method in VBA in Excel
- UsedRange Property in VBA in Excel
- VBA AutoFit (Rows, Column, or the Entire Worksheet)
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- VBA Copy Range to Another Sheet + Workbook
- VBA Enter Value in a Cell (Set, Get and Change)
- VBA Insert Column (Single and Multiple)
- VBA Named Range | (Static + from Selection + Dynamic)
- VBA Range Offset
- VBA Sort Range | (Descending, Multiple Columns, Sort Orientation
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- VBA Check IF a Cell is Empty + Multiple Cells