Excel VBA Color Index: Complete Guide to Fill Effects and Colors In Cells
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 using xlDialogEditColor 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 row Range("B2:D2").Interior.Color = rgbDarkGreen 'Using Color Constants - for a cell using row and column number Cells(3, 2).Interior.Color = vbYellow 'Specifying the RGB values - using A1 notation Range("B4").Interior.Color = RGB(255, 0, 0) 'Using Color Code - for few cells in a column Range("B5:B6").Interior.Color = 15773696 'Using Color Index - for a range Range("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 Next End 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 column Columns(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 to xlNone
'Remove color Range("A1").Interior.Color = xlNone
or you can set a cell to have automatic color using xlColorIndexAutomatic
'Set color to automatic Range("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 A1 Cells(1, 2) = Range("A1").Interior.Color
Example 6: Get the color input from the user using xlDialogEditColor
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.
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 dialog intResult = Application.Dialogs(xlDialogEditColor).Show(40, 100, 100, 200) 'gets the color selected by the user intColor = ThisWorkbook.Colors(40) 'changes the fill color of cell A1 Range("A1").Interior.Color = intColor End 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.
Sub multiColorStops() Dim objColorStop As ColorStop Dim lngColor1 As Long 'First create a gradient Range("A1").Interior.Pattern = xlPatternLinearGradient 'Changes orientation to vertical (default is horizontal) - optional Range("A1").Interior.Gradient.Degree = 90 'Clears all previous colorStop objects as they are at position 1 and 2 Range("A1").Interior.Gradient.ColorStops.Clear 'Start creating multiple colorStops at various positions from 0 to 1 Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0) 'Set the color for each colorstop objColorStop.Color = vbYellow Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0.33) objColorStop.Color = vbRed Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(0.66) objColorStop.Color = vbGreen Set objColorStop = Range("A1").Interior.Gradient.ColorStops.Add(1) objColorStop.Color = vbBlue End Sub
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
You can also change the color of the pattern using the code below:
Range("A1").Interior.PatternColor = vbBlue
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:
It will display 9 for the checker pattern that we have applied earlier
For more details, refer to the article Excel VBA, Fill Pattern