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.

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 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

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