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.

Excel VBA ColorIndex chart


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).

RGB color palette in xlRgbColor class

  • 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.

Dataset to create ColorIndex chart

Steps:

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

Create Module to write code

  • 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

VBA code to insert colors in the range

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 be the next color in the sequence.

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

Final output of ColorIndex chart

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.

Dataset to clear background 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

VBA code to clear background colors

Code Breakdown

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

The code accesses the interior property of each cell (i) in the range (j), representing the cell’s formatting background. Setting the ColorIndex property of the Interior to xlColorIndexNone removes the background color from that cell.

The background of each cell returned to its default color.

Cleared background color of dataset


How to Change Worksheet Tab Colors in Excel VBA

Change worksheet tab color

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

VBA code to change worksheet tab color

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

Inactive worksheet tab color

 


Download the Practice Workbook

Download this file to practice.


 

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hassan
Mehedi Hassan

Mehedi Hassan, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, functions as an Excel & VBA Content Developer at ExcelDemy. His deep interest in research and innovation aligns seamlessly with his fervor for Excel. In this role, Mehedi not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, emphasizing his unwavering dedication to consistently delivering outstanding content. His interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo