Excel VBA Color Index: Complete Guide to Fill Effects and Colors In Cells - VBA and VB.Net Tutorials, Education and Programming Services (2024)

In this article, let’s look at the various ways to set or remove the interior/background color of a cell or range – ie to fill the cell. We’ll also have a look at how to fill a cell/range with a pattern. Finally, we’ll review how to get the color input from the user usingxlDialogEditColor and working with color gradients.

Example 1: Set the color of a cell / range

The .Interior.Color property is used to set the color of a cell or a range. There are various methods in which we can do this.

'Using XlRgbColor Enumeration - for few cells in a rowRange("B2:D2").Interior.Color = rgbDarkGreen'Using Color Constants - for a cell using row and column numberCells(3, 2).Interior.Color = vbYellow'Specifying the RGB values - using A1 notationRange("B4").Interior.Color = RGB(255, 0, 0)'Using Color Code - for few cells in a columnRange("B5:B6").Interior.Color = 15773696'Using Color Index - for a rangeRange("B7:D8").Interior.ColorIndex = 7

This is how the output will look

For more details, refer to article Excel VBA, Cell Fill Color

Example 2: Set the color of a an entire row

You can use the .Interior.Color property on an entire row. Say you want to highlight all rows where the value of a column satisfies a condition:

Sub highlightRows() Dim rowNo As Integer For rowNo = 3 To 12 If Sheet1.Cells(rowNo, 3).Value < 30 Then Rows(rowNo).Interior.Color = vbRed End If NextEnd Sub

Here is the Excel before and after executing the code.

Example 3: Set the color of a an entire column

Similar to Example 2, you can fill an entire column using:

'Set color for columnColumns(2).Interior.Color = vbCyan

Example 4: Remove the color from a cell / range

You can also remove the background color of a cell by setting it toxlNone

'Remove colorRange("A1").Interior.Color = xlNone

or you can set a cell to have automatic color using xlColorIndexAutomatic

'Set color to automaticRange("A1").Interior.ColorIndex = xlColorIndexAutomatic

Example 5: Get the color code of a cell

You can also get the color code of a cell.The line below gets the color code of the color used to fill cell A1 and prints it in cell B1:

'gets the color code used to fill cell A1Cells(1, 2) = Range("A1").Interior.Color

Example 6: Get the color input from the user usingxlDialogEditColor

The xlDialogEditColor is a dialog used to get a color code from the user. This dialog has 2 tabs: Standard and which we will see soon.

Syntax:

intResult = Application.Dialogs(xlDialogEditColor).Show(intIndex, [intRed], [intGreen], [intBlue])

intResult: Zero if the user cancels the dialog and -1 if the user selects a color.
intIndex: Selected color when the edit color dialog is opened. It is also used as an identifier to get the value of the color selected by the user. (More details below).
intRed, intGreen, intBlue: Red, blue and green components of a color

There are 2 methods for calling this dialog:

1. Displaying the standard tab: If only the intIndex is specified and the last 3 parameters are omitted, standard tab will be displayed. intIndex will decide the color index initially selected in the standard tab. This is a number between zero and 56.

intResult = Application.Dialogs(xlDialogEditColor).Show(20)

2. The custom tab is initially displayed. If all the 4 parameters, including the RGB values, are specified, the custom tab will be displayed.

 intResult = Application.Dialogs(xlDialogEditColor).Show(20, 100, 100, 200) 

So, here is the complete code to get the color code from the user:

Sub changeColor()Dim intResult As Long, intColor As Long'displays the color dialogintResult = Application.Dialogs(xlDialogEditColor).Show(40, 100, 100, 200)'gets the color selected by the userintColor = ThisWorkbook.Colors(40)'changes the fill color of cell A1Range("A1").Interior.Color = intColorEnd Sub

Note:The intIndex specified in the xlDialogEditColor (40 in our example) is also the index used by ThisWorkbook.Colors. You need to make sure that these two numbers match.

Example 7: Gradient’s Colors

You can create a gradient using “xlPatternLinearGradient”

Range("A1").Interior.Pattern = xlPatternLinearGradient

It will look like this:

A gradient can have one or more colorStops, and each ColorStop has a position (a value between 0 and 1) and a color property.When you create a gradient, by default, the gradient has two ColorStop objects. One of the color stop objects has the position 1 and the other has the position 2.In order to be able to fully use the gradient properties in VBA, it is best to change the default positions to 0 and 1. In this way we would be able to have additional positions (colorStops) in between (i.e 0.5, 0.3). Let us now look at an example.

SubmultiColorStops()DimobjColorStopAsColorStopDimlngColor1AsLong'First create a gradientRange("A1").Interior.Pattern = xlPatternLinearGradient'Changes orientation to vertical (default is horizontal) - optionalRange("A1").Interior.Gradient.Degree = 90'Clears all previous colorStop objects as they are at position 1 and 2Range("A1").Interior.Gradient.ColorStops.Clear'Start creating multiple colorStops at various positions from 0 to 1SetobjColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0)'Set the color for each colorstopobjColorStop.Color = vbYellowSetobjColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0.33)objColorStop.Color = vbRedSetobjColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0.66)objColorStop.Color = vbGreenSetobjColorStop = Range("A1").Interior.Gradient.ColorStops.Add(1)objColorStop.Color = vbBlueEndSub

The final result will look like this.

For more details, please refer to the article Excel VBA, Gradient’s Colors

Example 8: Color Patterns

Using VBA, you can also apply various patterns to cells in Excel. The patterns available can be seen in the snapshot below (Right click on a Cell > Format Cells > Fill tab > Pattern Style):

The pattern of a cell can be changed using the xlPattern enumeration. The code below changes the pattern of cell A1 to the checker pattern:

range("A1").Interior.Pattern = XlPattern.xlPatternChecker

Result:

You can also change the color of the pattern using the code below:

Range("A1").Interior.PatternColor = vbBlue

Result:

You can get a complete list of the XlPattern Enumeration in Excel here.

To get the index (as specified in the link above) of the pattern applied in a cell use:

MsgBox Range("A1").Interior.Pattern

It will display 9 for the checker pattern that we have applied earlier

For more details, refer to the article Excel VBA, Fill Pattern