VBA Range Interior: Set Excel Cell Color and Patterns

In Excel VBA, a range’s Interior object controls its cell fill: set Range("B2").Interior.Color = RGB(255, 255, 0) to make B2 yellow. Use Interior.Pattern for solid fills or patterns, and Interior.ColorIndex when working with Excel’s indexed palette.

What Range.Interior refers to

Interior is a property of a VBA Range object. It represents the cells’ interior formatting—their background fill, not their font, borders, or displayed value. A range can be one cell, a rectangular block, a whole row or column, or a noncontiguous selection.

For example, Range("B2:D5").Interior refers to the fill formatting for every cell from B2 through D5. You can set a property on that object directly, or store the object in a variable when a macro needs to use it more than once. The range must refer to the intended worksheet; relying on whichever sheet happens to be active is a common source of mistakes.

A fully qualified reference makes the target clear:

Sub ColorReportHeader()
    Dim ws As Worksheet

    ' Target a specific worksheet rather than the active sheet.
    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Range("A1:F1").Interior.Color = RGB(31, 78, 121)
End Sub

This changes the fill of A1:F1 on the worksheet named Report in the workbook containing the macro. The code does not need to select the cells first.

Set a fill color with Color

Interior.Color reads or sets a fill as a numeric color value. The most readable way to specify an ordinary RGB color in VBA is the built-in RGB(red, green, blue) function. Each component is an integer from 0 through 255: RGB(255, 0, 0) is red, RGB(0, 0, 0) is black, and RGB(255, 255, 255) is white.

With a worksheet variable named ws already set to the target sheet, this example applies a pale yellow fill to a block:

Sub HighlightInputCells()
    Dim ws As Worksheet

    ' Change this sheet name to match the workbook.
    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Range("B2:D10").Interior.Color = RGB(255, 255, 153)
End Sub

Excel also accepts color constants and numeric color values, but RGB is usually easier to understand and adjust. The order matters: RGB takes red first, green second, and blue third. It is not a hexadecimal string, so a value such as "#FFFF00" should not be passed to .Color as though it were an RGB argument.

Choose between Color, ColorIndex, and theme colors

Several related properties can describe a fill color, but they are not interchangeable in every situation:

Color specifies the actual color value. Use it with RGB() when you need a particular color independent of the workbook’s palette.
ColorIndex refers to an indexed color in Excel’s palette system. It is useful when maintaining older workbooks, but an index can be less clear and less portable than an explicit RGB value.
ThemeColor refers to a workbook theme color. A theme-based color can follow a changed document theme, which is useful for coordinated designs.
TintAndShade adjusts the tint or shade of a color. It is relevant when you want a lighter or darker version of a theme color rather than a fixed RGB color.

For a simple macro that highlights cells with a specific color, .Color = RGB(...) is generally the most straightforward choice. If you are editing a workbook designed around theme colors, use its theme settings instead of hard-coding a color that may not match the rest of the workbook. You can inspect a cell’s current fill by reading its Interior properties in the Immediate window or assigning the values to variables while debugging.

Use Pattern for solid fills and hatch patterns

Interior.Pattern controls whether a fill is solid, patterned, or absent. For a conventional colored background, set the pattern to xlSolid and assign the color. Excel also provides pattern constants such as xlGray16; available patterns are defined by Excel’s XlPattern enumeration. Pattern fills can use both a background color and a separate pattern color.

Sub ApplyPatternFill()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Report")
    With ws.Range("F2:F6").Interior
        .Pattern = xlGray16
        .Color = RGB(255, 255, 255)
        .PatternColor = RGB(0, 102, 204)
    End With
End Sub

Here, the cells have a light patterned fill with a blue pattern color. The exact appearance depends on the selected pattern and the colors specified. If the requirement is just “make these cells blue,” use a solid fill rather than a hatch pattern.

Clear a fill without changing other formatting

To remove a cell’s fill, set its pattern to xlNone. This clears the interior fill while leaving values, number formats, fonts, and borders alone. For example, with ws referring to the desired worksheet:

Sub ClearReportFill()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Range("B2:D10").Interior.Pattern = xlNone
End Sub

Clearing a fill is different from clearing the contents of cells. A command that clears contents removes cell values or formulas; it is unnecessary if your goal is only to remove background color. Likewise, clearing all formatting may also remove borders and font styling you intended to keep, so use the narrowest operation that matches the task.

Target dynamic ranges and individual cells

A macro can apply interior formatting to a fixed address, but a report that grows over time may need a range based on its data. One option is CurrentRegion, which returns the connected block of cells around a starting cell. It stops at blank rows and columns, so it is suitable for a contiguous table with no completely blank separators, but it may include headers or nearby data depending on the layout.

Sub ShadeCurrentTable()
    Dim ws As Worksheet
    Dim dataRange As Range

    Set ws = ThisWorkbook.Worksheets("Report")
    ' Assumes the table is one connected block around A1.
    Set dataRange = ws.Range("A1").CurrentRegion
    dataRange.Interior.Color = RGB(242, 242, 242)
End Sub

For a single cell, use a direct address such as ws.Range("C4").Interior. For an entire column, ws.Columns("C").Interior targets every cell in column C; use that carefully because it can format a very large number of cells. If a range is discontiguous, such as Range("A1:A3,C1:C3"), Excel can apply a fill to all included areas, although code that reads a single property from a multi-area range can be less intuitive. When in doubt, loop through the individual areas or use separate range references.

Conditional formatting and apparent color

A cell’s visible fill may be coming from conditional formatting rather than its ordinary Interior formatting. For example, a rule may make values below zero appear red. Assigning Range("B2").Interior.Color changes the cell’s regular fill, but a conditional-formatting rule can still determine what appears on screen while its condition is true.

If a macro seems to set a color successfully but the cell continues to display another color, inspect the conditional formatting rules on that range. On Excel for Microsoft 365 on Windows, use Home > Conditional Formatting > Manage Rules to review them. The rule’s range, formula or condition, and priority can explain the displayed result. If the color should change according to cell values, conditional formatting may be a better solution than a macro because it updates as the values change.

Direct formatting and conditional formatting serve different purposes: use VBA when a task or event should apply a lasting format, and use a rule when formatting should respond dynamically to data. A workbook can contain both, so check the rules before concluding that the VBA assignment failed.

Common VBA Range.Interior problems

The wrong sheet changes: an unqualified Range("A1") refers to the active sheet in many macro contexts. Qualify the range with a worksheet variable, as in ws.Range("A1").
The color looks unexpected: check the RGB component order and values. Each component must be between 0 and 255, and the first argument is red.
The fill is not visible: check whether .Pattern is xlNone, whether conditional formatting is active, and whether the chosen color is similar to the cell background.
Only some cells change: confirm the address and any range-building logic. A blank row or column can split CurrentRegion into a smaller block than expected.
The macro reports an error: check that the worksheet name exists and is spelled exactly as shown in the sheet tab. Also confirm that the code is operating on a worksheet range, not a chart or another object type.

Use the VBA editor’s debugging tools to identify which statement fails. On Windows, press Alt+F11 to open the Visual Basic Editor; on a Mac, the shortcut is commonly Option+F11, sometimes with Fn depending on keyboard settings. You can also open Visual Basic from the Developer tab. Step through the macro and inspect the worksheet and range variables before changing formatting.

Run macros safely in Excel

VBA changes workbook formatting directly. Save a separate copy before running an unfamiliar macro: macro actions cannot be undone with Ctrl+Z. On Mac, the Undo shortcut is Command+Z, but it does not restore changes made by VBA either. Saving a copy provides a dependable way to return to the prior state if the macro targets too many cells or applies an unwanted format.

For Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and place the procedure there. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code. Macro security settings may restrict running code from untrusted files. Excel for the web can open a macro-enabled workbook, but it does not run VBA macros; use the desktop Excel application to execute them. Excel for Mac supports VBA, although some Windows-specific automation features and integrations are not available in the same way.

Frequently Asked Questions

How do I change a cell’s background color in VBA?
Set the range’s Interior.Color property, for example Range("A1").Interior.Color = RGB(0, 112, 192). Qualify the range with the worksheet when the macro should target a specific sheet.

How do I make a VBA fill color solid?
Set .Interior.Pattern = xlSolid, then set .Interior.Color to the desired RGB color. This makes the intended fill style explicit.

How do I remove cell color in VBA?
Set Range("A1").Interior.Pattern = xlNone. This removes the interior fill without clearing the cell’s contents or other formatting such as its font and borders.

What is the difference between Interior.Color and ColorIndex?
Color specifies a color value, commonly created with RGB(). ColorIndex refers to an indexed palette entry and is more associated with older palette-based workbooks.

Why does the cell still show a different color after my macro runs?
A conditional-formatting rule may be controlling the visible fill. Review the rules for the affected cells, and check that your macro targets the correct worksheet and range.

Can I use Range.Interior in Excel for the web?
VBA code cannot run in Excel for the web. To execute a VBA macro, open the workbook in desktop Excel for Windows or Mac; the VBA object model applies to those desktop applications.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.