How to Make and Use a ColorIndex Value Chart in Excel

Below is an image showing each color palette in ColorIndex and their RGB value.

Predefined Names of an Excel VBA ColorIndex

There are 8 predefined colors:

• vbBlack
• vbBlue
• vbCyan
• vbGreen
• vbMagenda
• vbRed
• vbWhite
• vbYellow

Enter these predefined colors in your code like below:

Range("A1").Interior.Color = vbBlue

Another list of predefined colors is under xlRgbColor class.

• Go to Object Browser in the Visual Basic Editor Toolbar or press F2 for access.
• Enter xlrgb or colorconstants in the Search Box. Press Enter.
• A list of predefined color variables will be displayed (as below).

• Enter the names in your code like below:

Range("A1").Interior.Color = rgbBisque

How to Create a ColorIndex Chart in Excel VBA

Below, we will change the background color in the cell range B5:I18 in alternative columns.

Steps:

• Access the Visual Basic Editor by pressing Alt + F11.
• Create a Module in the Visual Basic Editor from the Insert tab.

• Enter the code below in the module.
• Press F5, or from the Run tab, choose Run Sub/Userform.

Sub ApplyColorIndexToRange()
    Dim colorIndex As Long
    Dim rowIndex As Long
    Dim colIndex As Long
    Dim targetRange As Range

    Set targetRange = Range("B5:I18")
    colorIndex = 1

    For rowIndex = 1 To targetRange.Rows.Count
        For colIndex = 1 To targetRange.Columns.Count Step 2
            If colorIndex > 56 Then
                colorIndex = 1
            End If
            targetRange.Cells(rowIndex, colIndex).Interior.colorIndex = colorIndex
            colorIndex = colorIndex + 1
        Next colIndex
    Next rowIndex
End Sub

Set targetRange = Range("B5:I18")
colorIndex = 1

For colIndex = 1 To targetRange.Columns.Count Step 2

If colorIndex > 56 Then
      colorIndex = 1
End If

    targetRange.Cells(rowIndex, colIndex).Interior.colorIndex = colorIndex
    colorIndex = colorIndex + 1
Next colIndex

When you run the code, this is how your chart should look like.

Read More: Excel Color Index Numbers

How to Clear Colors in Excel VBA

Below is a dataset of student marks. We colored the marks red for students who achieved less than the passing mark. We will remove those colors.

Steps:

• Create a Module in the Visual Basic Editor from the Insert tab.
• Enter the below code in the module.
• Press F5 or choose Run Sub/Userform from the Run tab.

Sub RemoveBackgroundColor()
    Dim i As Range
    Dim j As Range

    Set j = Range("C6:E10")

    For Each i In j
        i.Interior.colorIndex = xlColorIndexNone
    Next i
End Sub

For Each i In j
     i.Interior.colorIndex = xlColorIndexNone
Next i

The background of each cell returned to its default color.

How to Change Worksheet Tab Colors in Excel VBA

Steps:

• Create a Module in the Visual Basic Editor from the Insert tab.
• Enter the below code in the module.
• Press F5 or choose Run Sub/Userform from the Run tab.

Sub changetabcolor() 

    'Changes the color of the worksheet
    ThisWorkbook.Sheets("Worksheet Tab Color").Tab.colorIndex = 45

End Sub

When the Worksheet is active, the color becomes light. But the Worksheet is inactive. It takes the actual color of the code.

Download the Practice Workbook

Download this file to practice.

Related Articles

• Excel VBA to Copy Cell Background Color
• How to Highlight Blank Cells in Excel VBA
• Excel VBA to Highlight Cell Based on Value
• How to Highlight Active Row in Excel VBA