Excel VBA ColorIndex Chart

In this article, you will learn how to create an Excel VBA ColorIndex chart. You will also learn about the ColorIndex value which will let you create an amazing looking Excel worksheet.

With 56 colors available in the palette, ColorIndex enables users to easily apply various colors to cells, and fonts. In this article, we will explore the fundamentals of ColorIndex, and learn how to use it to enhance the visual appeal of data and charts.

ColorIndex Value Chart in Excel VBA

In Excel VBA, one of the ways you can set the color of a cell is the ColorIndex property. This property can be applied to the Interior property (for filling the cell background) or the Font property (for changing the font color) of a cell or a range. ColorIndex can take values from 1 to 56, each representing a specific color.

We have an image here showing each of the color palettes that exist in ColorIndex and also their RGB value.

Predefined Names of Excel VBA ColorIndex

You can assign certain colors in Excel without using ColorIndex value. These colors are called Predefined Color. You can just use their names to assign them to your range or cell. There are 8 predefined colors in Excel. They are:

vbBlack
vbBlue
vbCyan
vbGreen
vbMagenda
vbRed
vbWhite
vbYellow

You can use these predefined colors in your code like this.

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

There is another list of predefined colors that exist under xlRgbColor class.

Go to Object Browser in the Visual Basic Editor Toolbar or press F2 to get access.
You will find a Search Box. Type xlrgb or colorconstants in the box and press Enter.
You will find a whole list of predefined color variables here.

Use the names in your code like this.

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

How to Create ColorIndex Chart in Excel VBA

We will try to create a ColorIndex chart using Excel VBA which will change the background color in the range B5:I18 in alternative columns. We already put the ColorIndex value in columns C, E, G, and I.

We will try to give simple explanations for the code so that you can understand them as you go.

First, access the Visual Basic Editor by pressing Alt + F11 on your keyboard.
Create a Module in the Visual Basic Editor from the Insert tab.

Now, write this code in the module.
Press F5 or from Run tab, choose Run Sub/Userform to run the code.

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

Code Breakdown

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

We set the targetrange variable to B5:I18 and colorIndex value will initialize from 1.

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

The code uses nested loops to iterate through each row and every other column within the targetRange.

If colorIndex > 56 Then
      colorIndex = 1
End If

The code checks if the colorIndex has exceeded the maximum value of 56 (the number of available colors in the palette). If the colorIndex is greater than 56, it is reset back to 1, so the sequence of colors starts again from the beginning.

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

The code accesses each cell in the targetRange using targetRange.Cells(rowIndex, colIndex), and it assigns the current colorIndex to the cell’s interior (background) color using the value of colorIndex. Then, the colorIndex is incremented by 1, so the next cell in the range will have the next color in the sequence.

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

If you want to remove any background color from your cells, you can also use ColorIndex property in Excel VBA.

We have a dataset of obtained marks for a couple of students. We colored the marks that were less than the pass marks. Our objective is to remove those colors.

Again, create a Module in the Visual Basic Editor from the Insert tab.
Now, write this code in the module.
Press F5 or from Run tab, choose Run Sub/Userform to run the code.

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

Code Breakdown

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

For each cell (i) in the range (j), the code accesses its interior property, which represents the cell’s background formatting. By setting the ColorIndex property of the Interior to xlColorIndexNone, it removes the background color from that cell.

As you can see, the background of each cell went back to its default color.

How to Change Worksheet Tab Color in Excel VBA

You can use the ColorIndex property to change the worksheet tab color. As you can see, it is now in default format.

Again, create a Module in the Visual Basic Editor from the Insert tab.
Now, write this code in the module.
Press F5 or from Run tab, choose Run Sub/Userform to run the code.

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 a little light. But the Worksheet is inactive, it takes the actual color of the code.

Frequently Asked Questions

1. What is the default color represented by ColorIndex = -4142 in Excel VBA ColorIndex chart?
Ans: The default color represented by ColorIndex = -4142 in Excel VBA is No Color or Automatic Color. It means that the chart element will use the default color defined by Excel for that specific element, depending on the chart type and formatting settings.

2. What happens if I use a ColorIndex value that exceeds the available colors in the palette?
Ans: If you use a ColorIndex value that exceeds the available colors in the palette for charts (1 to 56), Excel will loop back to the beginning of the color palette. For example, if you use ColorIndex = 57, Excel will treat that as ColorIndex = 1, and the color sequence will repeat.

3. Can I use custom colors with ColorIndex in VBA?
Ans: No, you cannot directly use custom colors with ColorIndex in VBA. It is limited to the available colors in the palette (1 to 56). To use custom colors, you should use the RGB function to specify the desired color using Red-Green-Blue values in VBA.

Download Practice Workbook

Download this file to practice with the article.

VBA ColorIndex Chart.xlsm

Conclusion

In conclusion, Excel VBA ColorIndex chart serves as a practical solution for managing colors in Excel workbooks. While ColorIndex is limited to the predefined palette, combining it with other VBA functions, like RGB, allows for greater flexibility in applying custom colors. We hope this article will help you understand and use ColorIndex in your VBA code and make your worksheet look cooler.

Get FREE Advanced Excel Exercises with Solutions!