Excel Color Index Numbers

In this article, we will get Excel color index numbers using Excel VBA. Here, we will learn how to get color index numbers in Excel and how to change the background color, interior color, font color, and border color. Here we will also learn how to clear the color index using Excel VBA.

Coloring cells is a vital part of completing a dataset. Distinct colors represent different meanings in a dataset, and that gives better visuality and clarification about the dataset. Getting color index numbers helps identify the color and completely differentiates colored cells from other cells.

Download Practice Workbook

You May download the below workbook for practice

How to Get Color Index Numbers Using VBA: 4 Methods

Here, we will learn how to get color index numbers using VBA. Follow the below program to get the color index numbers.

• Initially, go to Developer >> Visual Basic and open the VBA window.
• Then, go to Insert >> Module to write the code and get the color index with the numbers.

Sub ColorIndexNumbers()
startRow = 5
startColumn = 2
For iStart = 1 To 56
Cells(startRow, startColumn).Interior.colorIndex = iStart
Cells(startRow, startColumn) = iStart
If iStart > 1 And iStart Mod 14 = 0 Then
    startColumn = startColumn + 1
    startRow = 5
Else
startRow = startRow + 1
End If
Next
End Sub

• After all, the final output will be like the one below.

Sub ColorIndexNumbers()
startRow = 5
startColumn = 2
For iStart = 1 To 56

Cells(startRow, startColumn).Interior.colorIndex = iStart
Cells(startRow, startColumn) = iStart
If iStart > 1 And iStart Mod 14 = 0 Then
    startColumn = startColumn + 1
    startRow = 5
Else
startRow = startRow + 1
End If
Next
End Sub

How to Change Color with Index Number Using Excel VBA

There are different ways to change the color index using Excel VBA. You can moderate the outlook of the dataset by changing the color index using Excel VBA.

1. Change the Background Color

Here, we will change the background color using Excel VBA

• Write the code using the property colorIndex and select a particular color to change the background.

Sub BackgroundColour()
Range("D5:D12").Interior.colorIndex = 43
End Sub

• Therefore, the final output will be as below.

2. Change Font Color

Now, we will change the font color using the same process already shown in the previous method. But instead of using the Interior.colorIndex property, use the Font.colorIndex property to change the font color.

Sub FontColor()
    Range("D5:D12").Font.colorIndex = 10
End Sub

• Finally, the output is showing Green color as the font color in the cell range D5:D12.

3. Change Cell Border Color

This method is also similar to the previous ones. Here we will use the Borders.colorIndex property to change the cell border color.

Sub Borders()
Range("D5:D12").Borders.colorIndex = 32
End Sub

• Therefore, the cell border color will change according to the color index numbers below.

4. Change Interior Color RGB

In this part, we will change the interior color using the Interior.colorIndex property, or there is another option of using the RGB property to change the interior color in VBA.

Sub RGB()
Range("D5:D12").Interior.colorIndex = 36
End Sub

• The interior color will change according to the index color number. Choose another color by choosing another index color number.

How to Clear Color Index Using Excel VBA

In this part will be clear the color index. Sometimes the color index is unnecessary, and we can remove the color using a VBA program.

• In this case, select the colorIndex property and -4142 number to remove the color.

Sub clearBackground()
Range("D5:D12").Interior.colorIndex = -4142
End Sub

• Here is the final output after removing the color from the background.

Things to Remember

• There is a color ratio for all the colors you use in RGB, so if you want a specific color, try to remember that ratio of colors.
• You can change the color index using the toolbar, but in terms of repetitive work, productivity will be quite low if this work is done manually.

Frequently Asked Questions

Q1: What are Excel color codes?

Ans: Excel color codes represent RGB (Red, Green, and Blue) in this three-color palette, where the intensity is between 0 and 255.

Q2: What is the color index of red in Excel?

Ans: The color palette of Red varies with the number of colors. When you need Red, the intensity is 255. When you need Blue or Green, the intensity is 0 (Zero); on the other hand, the intensity is again 255 when you need Yellow.

Q3: What is the color index of green in Excel?

Ans: The intensity of Green is highest when you need colors like Green or Yellow and lowest when you need colors like Blue or Magenta.

Conclusion

Here, we learned about Excel color index numbers. Every color represents a number in Excel; if we change the number while adding the VBA code, then the colors will change as well. Here, we learned how to add color index numbers, and how to change background color, fill color, border color, and interior color in Excel using the Excel VBA. We cover every way to execute this process. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions, or you can also visit Exceldemy to explore more.