VBA Excel, Colors

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:

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:

Excel, VBA Color COde

After

Excel, VBA Color Code 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:

Excel VBA, ColorConstants
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:

Excel, VBA, RGB Color Constants
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:

Excel VBA, RGB, Before
After:

Excel VBA, RGB, After

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

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

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