VBA Excel, Colors

Aug 03, 2015 by azurous in Color

This article explain how you can work with colors in VBA for Excel. When working with Excel there are many cases when you will need to specify a color:

When selecting a fill color
When changing the font color
When changing the border color
…

To specify a color in VBA, you will need the color code. There are different methods for specifying colors. These methods will be explained briefly in this article.

Jump To:

Color Code
Class ColorConstants
XlRgbColor
RGB

–

Contents

Color Code:

Every color has a code. If you know the code you can specify the color directly. In the example below the color of the cell A1 is changed to the color associated with the code 1156:

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

Before:

After

For an example using color codes please see Excel VBA, Color Code.

The Excel VBA, Get Color Code provides a VBA application for getting the color code for a color.

You can also get the color code using the macro recorder. For more information on this topic please see Excel VBA Getting the Color Code Using the Macro Recorder.

–

Class ColorConstants:

You can specify colors in VBA using color constants. The class ColorConstants contains a list of constants with color codes. In the figure below you can see the members of the ColorConstants class, with their associated value and color:

For an example using the class ColorConstants please see Excel VBA, ColorConstants.

–

XlRgbColor:

Another list of color constants is provided by the enumeration XlRgbColor. The enumeration contains a list of color codes. Unlike the ColorConstants class which only contains 8 color codes, the enumeration XlRgbColor contains around 50 different color codes. Below you can see the first 7 color codes and their associated color in the XlRgbColor enumeration:

For more information about the XlRgbColor enumeration please see Excel VBA, XlRgbColor.

–

RGB:

One method for specifying a color is using the RGB color model. In this model colors are defined based on the amount of Red, Green and Blue light in them. VBA provides the function RGB() for this purpose. The function receives as input 3 integers. These integers determine the amount of Red, Green and Blue in the color. The function then returns the color code associated with that color. For example:

Range("A1").Interior.Color = RGB(100, 100, 0)

Before:

After:

For more information about using the RGB() function please see Excel VBA, RGB.