How to set border colors of Excel Cells in VBA
- You may use Borders.ColorIndex property to set the border color from the available 56 color palette
- Excel cell border color can also be set with RGB colors by using Borders.Color property
- The examples of using both ways are shown below with resultant sheets
An example of setting the border color by ColorIndex property
The ColorIndex property enables you to use the default color palette index from 1 to 56 values as shown below.
The example below shows setting the color of seven cells (A2 to G2) in our Excel sheet by using the ColorIndex property:
Sub border_color()
Range("A2").Borders.ColorIndex = 5
Range("B2").Borders.ColorIndex = 10
Range("C2").Borders.ColorIndex = 21
Range("D2").Borders.ColorIndex = 54
Range("E2").Borders.ColorIndex = 55
Range("F2").Borders.ColorIndex = 44
Range("G2").Borders.ColorIndex = 53
End Sub
See all default colors in the Excel sheet by For..Next loop
In this example,
- We created a range of cells consisting of 56 cells.
- In each cell, we will assign a separate color code by ColorIndex property to each cell’s border.
VBA Code:
Sub border_color()
Dim Border_rng As Range, Border_cell As Range
Dim x As Integer
x = 1
'56 cells to hold all default palette colors
Set Border_rng = Range("A1:G8")
For Each Border_cell In Border_rng
Border_cell.Borders.ColorIndex = x
Border_cell.Value = x
x = x + 1
Next Border_cell
End Sub
Result:
Using RGB color in Borders.Color property
If you need more color options beyond the 56 default color palette then you may set the color by RGB code.
VBA Borders.Color property can be used to set the color with RGB value.
The example below sets the color of five cells with RGB values:
VBA code:
Sub border_color()
'Setting RGB values of Cells Borders
Range("A2").Borders.Color = RGB(207, 158, 158)
Range("B2").Borders.Color = RGB(140, 0, 0)
Range("C2").Borders.Color = RGB(117, 255, 152)
Range("D2").Borders.Color = RGB(255, 9, 9)
Range("E2").Borders.Color = RGB(168, 168, 255)
End Sub
Output:
