Change the Background Color of Cells using VBA

Today, we will learn the important functionality of formatting the cells using VBA. We will change the background color of the cell or range with the help of VBA. There are different ways to change the background color of cells and we will implement all of them using examples.

So let us start learning.

Change the Background Color of Cells

There are a number of ways to change the background color of the cells with the help of VBA. We can use the ColorIndex property of Cell or Range. Another way is to use the Color property with the RGB function to specify the individual color component for the background color of the cells. RGB function used with the Color property is more flexible than the ColorIndex property and provides many color combinations.

Using the RGB function with the Color property, we can have 16,777,216 colors whereas the ColorIndex value ranges from 1 to 56 providing 56 color background options.

You can use the following practice workbook to follow along with us.

ColorIndex Property – Cell Interior

We need to use the Cell or Range with the Interior property followed by the ColorIndex property. The value of ColorIndex must lie between 1 and 56.

Use the following syntax to change the background color of a Range.

Range(<range_to_format>).Interior.ColorIndex=<color_index_value>

Here Cells is used to refer to a single cell followed by the intersection of a row and column.

Cells(<row_number>,<col_number>).Interior.ColorIndex=<color_index_value>

Using the RGB Function

RGB function provides a variety of color combinations. We need to specify the Red, Green, and Blue color component values to form the resultant color.

Range(<range_to_format>).Interior.Color=RGB(<red>,<green>,<blue>)

Here each of the color components can vary from 0 to 255 (inclusive).

Some of the colors formed with the following values of Red, Green, and Blue color components are:-

Examples to Change Background Color

In this section of the blog, we will implement some practical examples to change the background color of the cells or range using VBA. This property is useful to make dashboards and reports with the help of VBA.

Example 1 – Using ColorIndex Property

We can use the Cells function to refer to an individual cell. The Interior refers to the Interior of the object and then we specify the ColorIndex to be any value between 1 and 56.

Use the following Sub procedure to change the background color of Cell A1.

Sub ChangeBackColorUsingColorIndex()
Cells(1,1).Interior.ColorIndex=29
End Sub

When you run this macro, it changes the background color of cell A1 of the active worksheet.

Example 2 – Generate Color Pallete of ColorIndex

We can see the different colors formed by each possible value of the color index. We have used nesting of For Loop. You can use the following macro and also assign it to a command button to generate a palette of colors in the range A1:H7.

Sub ShowColorPallete()
'generate color palelte using ColorIndex property
Dim i As Integer
Dim j As Integer
Dim x As Integer
x = 1
For i = 1 To 7
    For j = 1 To 8
        Cells(i, j).Interior.ColorIndex = x
        Cells(i, j).Value = x
        x = x + 1
    Next j
Next i
End Sub

When you run this macro, a color palette is produced along with each index of color in the cell.

Example 3 – Using RGB Function to Change Background Color of Non Empty Cells

We can use the RGB Function when we cannot find the required color in the 56 options of the ColorIndex property. We use the RGB function with the Color Property of the Cell or Range to change the interior color.

Use the following Sub procedure to change the background color of nonempty cells in the range A1:D10.

Sub HighLightNonEmptyCellsA1D10()
Dim r As Range
Dim c As Range
Set r = Range("A1:D10")
For Each c In r
  If IsEmpty(c) Then
  'nothing happens
  Else
  'if cell is not empty
  c.Interior.Color = RGB(175, 238, 238)
  End If
Next c
End Sub

Here we have used the For Each loop to loop through each cell (c) in range (r).

Example 4 – Generate Different Shades of Color using RGB Function

We can generate different shades of color. In this code, we will input a code and then get its lighter shades by changing the value of the Red, Green, and Blue Color components. This is known as changing the tint of color to get lighter shades.

In this code, we first input the value of the Red, Green, and Blue Color components and then the color generated becomes the background color of cell A1. Thereafter we update the RGB color component using the following formula in each loop iteration.

red = red + (0.25 * (255 - red))
green = green + (0.25 * (255 - green))
blue = blue + (0.25 * (255 - blue))

Use the following Sub Procedure to implement the work.

Sub getLighterShadeOfColor()
Dim red As Integer
Dim green As Integer
Dim blue As Integer
'input the value of red, green and blue color component
red = InputBox("Enter red component value:")
green = InputBox("Enter green component value:")
blue = InputBox("Enter blue component value:")
Dim c As Range
Dim r As Range
'set the value of range
Set r = Range("A1:A10")
For Each c In r
    'change the interior color of cell
    c.Interior.Color = RGB(red, green, blue)
    'update the value of RGB color component to get lighter shade
    red = red + (0.25 * (255 - red))
    green = green + (0.25 * (255 - green))
    blue = blue + (0.25 * (255 - blue))
Next c
End Sub

Enter the Value of the RGB color component and then the code will change the background color of cells in range A1:A10 to the lighter shades of generated color.

This brings us to an end.

Thank you for reading. ❤