Excel/VBA Interior/Background Color by ColorIndex/Color

How to set the interior color of Excel cells using VBA

• The interior (background) color of the cells in the Excel sheet can be set by using the VBA ColorIndex property.
• The default color pallet has 1-56 values – that contain all major colors. See the image below:

• If you do not find the color of your choice or want to broaden your options, you may also set the color by using RGB code (shown in the example in the coming section)

An example of setting the interior color of cells

The example below sets the interior color of A1 to E1 cells by using the ColorIndex property. The code and resultant sheet are below:

Sub interior_color()

  Range("A1").Interior.ColorIndex = 3

  Range("B1").Interior.ColorIndex = 6

  Range("C1").Interior.ColorIndex = 11

  Range("D1").Interior.ColorIndex = 38

  Range("E1").Interior.ColorIndex = 56

End Sub

Result:

Using For loop to use all color values

In this example, we will create a color pallet of available default values in our sample sheet.

For that,

• We will create a range of 56 cells (A1:G8).
• Use a For Next loop
• Refer to each cell in the range and assign a color by the ColorIndex property
• Also, write the color value by using Range.Value

Have a look at the VBA code and output:

Sub interior_color()
Dim rng As Range, cell As Range
Dim i As Integer

i = 1

'56 cells to hold all default palette colors
Set rng = Range("A1:G8")

For Each cell In rng

    cell.Interior.ColorIndex = i

    cell.Value = i

    i = i + 1

Next cell


End Sub

Result:

Using RGB color code example

Fifty-six colors not enough to fulfill your needs? No problems. Just like website options for choosing among lots of colors, you may also set the color of Excel cells.

The cell interior color can be set by using Interior.Color property. As the value, you may provide an RGB color code.

RGB stands for Red, Green, and Blue.

In the example below, we set the color of five cells (A1 to E1) by using RGB color codes:

VBA code:

Sub interior_color()

‘Setting RGB values      

  Range("A1").Interior.Color = RGB(207, 158, 158)

  Range("B1").Interior.Color = RGB(255, 166, 166)

  Range("C1").Interior.Color = RGB(117, 255, 152)

  Range("D1").Interior.Color = RGB(255, 9, 9)

  Range("E1").Interior.Color = RGB(168, 168, 255)


End Sub

Result: