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:
Dim rowNo As Integer
For rowNo = 3 To 12
If Sheet1.Cells(rowNo, 3).Value < 30 Then
Rows(rowNo).Interior.Color = vbRed
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
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:
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
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.
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
'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
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