Set the Border Color of Excel Cells by VBA Color/ColorIndex Properties
You may use Borders.ColorIndex property to set the border color from available 56 color palette
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 seperate color code by ColorIndex property to each cell’s border.
VBA Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 by 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
