Colorindex in Excel VBA

Complete Excel VBA Course

Today let’s try to understand how ColorIndex property in Excel VBA works. It is an easy and effective way to quickly complete the development. ColorIndex property is normally used by VBA developers to fill cell, border and font colors. ColorIndex returns values from 1 to 56, -4105 and -4142.

 

Excel VBA Color Index: Color Variations

From index 1 to 56 are actual colors which provides various types of colors. Have a look at below color variations

Complete Excel VBA Course
ColorIndex in Excel VBA

Excel VBA Colorindex : Code

Let us now see how we can implement ColorIndex in VBA codes:

ColorIndex in Excel VBA

Code

Sub ColorIndexExample1()

    Sheet1.Range("C2").Interior.ColorIndex = 1 'Black
    Sheet1.Range("C3").Interior.ColorIndex = 3 'Red
    Sheet1.Range("C4").Interior.ColorIndex = 5 'Blue

End Sub

Explanation:

Here we have set the color of the cell using ColorIndex. ColorIndex = 1 is used to change the cell color to Black. Similarly, ColorIndex 2 and 3 are used to change the cell color to Red and Blue respectively. ”Example 2: Changing Border Color” 

Code:

Sub ColorIndexExample2()

    Sheet1.Range("C2").Borders.ColorIndex = 3 'Red
    Sheet1.Range("C3").Borders.ColorIndex = 4 'Green
    Sheet1.Range("C4").Borders.ColorIndex = 6 'Yellow

End Sub

Explanation:

In the above code, we have changed the border color of the cells using ColorIndex property available under Borders function of Range. ”Example 3: Changing Font Color” 

ColorIndex in Excel VBA

Code:

Sub ColorIndexExample3()

    Sheet1.Range("B2").Font.ColorIndex = 1 'Black
    Sheet1.Range("B3").Font.ColorIndex = 3 'Red
    Sheet1.Range("B4").Font.ColorIndex = 5 'Blue

End Sub

Explanation:

In this third example, we have changed font color of the cell using ColorIndex property.

Both -4105 and -4142 are special enumeration to set the ColorIndex to Automatic or None. If ColorIndex is supplied as -4105 (xlColorIndexAutomatic) then cell color, border or font is set to Automatic. Below is a sample code

 
ColorIndex in Excel VBA

Code:

Sub ColorIndexExample4()

    Sheet1.Range("C2").Interior.ColorIndex = xlColorIndexAutomatic

End Sub

Explanation:

The above code changes the cell color of C2 on Sheet1 to Automatic which is white here. Similarly, -4142 (xlColorIndexNone) is used to set ColorIndex to none. Below is a sample code of the same:

 
ColorIndex in Excel VBA

Code:

Sub ColorIndexExample5()

    Sheet1.Range("C3").Interior.ColorIndex = xlColorIndexNone

End Sub

Explanation:

The above code changes the cell color of C3 on Sheet1 to None.=”How to Get ColorIndex of a Cell?” Below is sample code to get the ColorIndex value of a Cell Color, Font or Border:

ColorIndex in Excel VBA

Code:

Sub ColorIndexExample6()

    Sheet1.Range("C2").Value = Sheet1.Range("B2").Interior.ColorIndex 'Cell ColorIndex
    Sheet1.Range("C3").Value = Sheet1.Range("B3").Font.ColorIndex 'Font ColorIndex
    Sheet1.Range("C4").Value = Sheet1.Range("B4").Borders.ColorIndex 'Border ColorIndex

End Sub

Explanation:

In the above code, we are reading the ColorIndex of Cell, Font, and Border Colors the value of the ColorIndex is then stored back in the sheet in the column.  You can also make use of few color Constants defined in Excel VBA for quick reference.

ColorIndex in Excel VBA

Code:

Sub ColorConstantsExample()

    Sheet1.Range("C2").Interior.Color = vbBlack
    Sheet1.Range("C3").Interior.Color = vbBlue
    Sheet1.Range("C4").Interior.Color = vbCyan
    Sheet1.Range("C5").Interior.Color = vbGreen
    Sheet1.Range("C6").Interior.Color = vbMagenta
    Sheet1.Range("C7").Interior.Color = vbRed
    Sheet1.Range("C8").Interior.Color = vbWhite
    Sheet1.Range("C9").Interior.Color = vbYellow

End Sub

There is also one more way to work with colors using RGB (Red Green Blue) function. The function requires 3 numbers from 0 to 255 [e.g. RGB(23,122,98)]. Have a look at below code:

ColorIndex in Excel VBA 
Sub RGBExample()

    Sheet1.Range("B2").Interior.Color = RGB(255, 0, 0) 'Red
    Sheet1.Range("B3").Interior.Color = RGB(0, 255, 0) 'Green
    Sheet1.Range("B4").Interior.Color = RGB(0, 0, 255) 'Blue

End Sub

Explanation:

In the above code, we are using RGB VBA function to change the cell colors.

Have a look at How to get RGB Codes of a Color and VBA Code to Change Cell Color post to know more about this.

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.
Download Practice File

Recommended Articles

Excel VBA Course : Beginners to Advanced

We are currently offering our Excel VBA Course at discounted prices. This course includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion

This course is going to help you to excel your skills in Excel VBA with our real time case studies.

Lets get connected and start learning now. Click here to Enroll.

Secrets of Excel Data Visualization: Beginners to Advanced Course

Here is another best rated Excel Charts and Graph Course from excelsirji. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.

This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.

So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.

Use Automated Excel Utility Tools to increase productivity

Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *