Set the Border Color of Excel Cells by VBA Color/ColorIndex Properties

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

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: