VBA – Color (ColorIndex)

Colors and ColorIndex

Colors could be very important and useful in excel application. There are two ways in which you can get or set the colors in VBA Excel. They are: ColorIndex, which has 56 colors, and Color, which has millions of colors and makes it possible to use any color at all.

ColorIndex

The ColorIndex is also a property. It is used to assign a certain color to some elements, such as a cell’s background or its text, from a palette of 56 pre-defined colors, via VBA programming.

In order to use this ColorIndex property, you will need to know each color’s corresponding value in the color palette. You can find out these values by running the following macros:

VBA - Color (ColorIndex)

Fig 2.7: Getting the color palette

VBA - Color (ColorIndex) VBA - Color (ColorIndex)

Fig 2.8: Color Palette with the corresponding numbers

We can now use this color palette to set the color of any cell, group of cells or text.

As an example, we will set the color of cell “B3” using a value from the palette. Copy the following macros to your VB Editor:

VBA - Color (ColorIndex)

Fig 2.9: Macro for colorIndex

VBA - Color (ColorIndex)

Fig2.1.0: result

Excel RGB color

The other method of using the color property in Excel is through the RGB color property “RGB” stands for Red Green Blue, which are known as three primary colors, which can be combined to produce other colors. For example, purple is a mix of blue and red.  In RGB Property, you need to give a value (from 0 to 255) for each color in order to mix a new color. For example, Red = 255 means the brightness of color red is 100%. If notice, we’ve been using this method to access the color property of Excel. The below macro changes the color of the selected cells using this RGB method;

VBA - Color (ColorIndex)

Fig 2.1.1: RGB Macro

VBA - Color (ColorIndex)

Fig 2.1.2: Result

However, these are just few of what you can do with all the discussed methods and properties.

Was this article helpful?

Related Articles

Leave a Reply