To change a cell’s background color with Excel VBA, set its Interior.Color property—for example, Range("A1").Interior.Color = RGB(255, 255, 0) fills A1 yellow. Use Interior.ColorIndex for indexed colors, or theme properties when the fill should follow the workbook’s theme.
Set a cell fill with Interior.Color
The usual way to color a cell in Microsoft Excel for Microsoft 365 on Windows is to assign a color to the cell’s Interior.Color property. The RGB function takes red, green, and blue values, each from 0 to 255. For instance, 255, 255, 0 combines full red and green with no blue, producing yellow.
This small macro colors A1 and demonstrates the basic syntax. In the Visual Basic Editor, choose Insert > Module, paste the code into the module, and run the macro while the relevant workbook is active.
Sub ColorOneCell()
' Fill A1 with a bright yellow background.
Range("A1").Interior.Color = RGB(255, 255, 0)
End Sub
Before running any macro, save a copy of the workbook. VBA changes to cell formatting cannot be undone with Ctrl+Z, so a backup is the simplest way to recover if a macro affects the wrong cells.
How Excel cell fill color works
A cell’s fill is part of its Interior formatting object. That object also exposes properties for patterns and related fill settings. It is separate from the cell’s value, number format, font color, and border. Changing Interior.Color changes the background fill; it does not change the text color or the contents of the cell.
Use RGB(red, green, blue) rather than trying to calculate the numeric color value yourself. For example, RGB(0, 112, 192) produces a blue often used in office-style palettes, while RGB(0, 0, 0) is black and RGB(255, 255, 255) is white. The precise appearance can vary with display settings, but the stored RGB values remain the same.
To change text instead of the background, use Font.Color; for example, Range("A1").Font.Color = RGB(255, 0, 0) makes the font red. This distinction matters when a macro seems to run correctly but colors the wrong part of a cell.
Color a range or apply different colors
You can set the fill for a whole range at once. Excel applies the same fill to every cell in the specified range, which is usually quicker and clearer than looping through each cell when all cells need the same color.
Sub ColorInputRange()
' Shade the entry cells light blue.
Range("B2:B20").Interior.Color = RGB(221, 235, 247)
End Sub
For different fills based on cell contents, loop through the cells and test each value. The following example colors values below 50 pale red and values of 50 or more pale green. It checks that each cell contains a number, so text and empty cells are left alone.
Sub ColorScores()
Dim cell As Range
' Apply a fill based on each numeric score in B2:B20.
For Each cell In Range("B2:B20")
If IsNumeric(cell.Value) And Len(cell.Value) > 0 Then
If cell.Value < 50 Then
cell.Interior.Color = RGB(255, 199, 206)
Else
cell.Interior.Color = RGB(198, 239, 206)
End If
End If
Next cell
End Sub
Adjust the range and threshold to fit the worksheet. If values are formulas, VBA tests their current calculated results. If a range includes headers or notes, narrow the range or add a condition so those cells are not formatted unintentionally.
Choosing RGB, ColorIndex, or a theme color
There are three common ways to specify a fill, and the best choice depends on whether you need a precise color or a fill that can adapt to a workbook’s design.
Interior.ColorwithRGB: Best for a specific, repeatable color. The assigned RGB value stays the same if the workbook theme changes.Interior.ColorIndex: Uses an indexed color, such asColorIndex = 6for a yellow in Excel’s indexed palette. It can be convenient in older macros, but the index is less descriptive and less flexible than an RGB value.Interior.ThemeColorandTintAndShade: Best when a fill should be based on the workbook’s theme. A theme-based fill can change appearance when the workbook theme changes.
For a theme-based example, the following code assigns the first accent color and makes it lighter. The tint value ranges from -1 to 1; positive values lighten a theme color, while negative values darken it. The exact result depends on the active workbook theme.
Sub ApplyThemeFill()
' Use the first accent color with a lighter tint.
With Range("A1").Interior
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.6
End With
End Sub
Use RGB when a precise shade is important, such as when matching a required brand color. Use theme colors for templates intended to be restyled. Avoid relying on a numeric ColorIndex in a new macro unless you specifically need indexed-palette behavior.
Read, change, or remove a fill
To inspect a cell’s directly assigned fill color, read Interior.Color. The result is a numeric color value; for debugging or comparison, it is usually simpler to compare it with the same RGB expression used to set it than to interpret the number manually.
To remove a cell’s fill, set its interior pattern to xlNone. This removes the fill pattern rather than setting the background to white. That difference matters: a white fill is still a fill and can cover a colored background behind the cell, while no fill allows the worksheet’s default background to show.
Sub ClearCellFill()
' Remove the fill pattern from A1.
Range("A1").Interior.Pattern = xlNone
End Sub
Clearing the fill does not clear borders, font formatting, or values. To remove only the fill from a larger region, specify that region directly, such as Range("B2:B20").Interior.Pattern = xlNone. Avoid using a broad command such as Cells unless you intentionally want to affect the entire worksheet.
Direct fills and conditional formatting
A direct VBA fill is appropriate when the color should be set by a macro and remain until another action changes it. Conditional formatting is often a better fit when the color should automatically respond to changing values. For example, a rule can shade a score below 50, and Excel updates the display when the score changes.
In Excel, conditional formatting rules can be managed from Home > Conditional Formatting. A VBA assignment to Interior.Color sets the cell’s direct fill; it does not create or edit a conditional formatting rule. If a conditional formatting rule applies to the cell, its displayed fill may take precedence over the direct fill.
To read the fill that Excel is currently displaying, including formatting caused by conditional formatting, VBA can use DisplayFormat.Interior.Color. This is useful for checking a visible result, but it is not a way to set the conditional format’s appearance. To change the rule, edit the conditional formatting rule itself rather than trying to assign to DisplayFormat.
Common problems and how to fix them
- The wrong cell changes: An unqualified
Range("A1")refers to the active worksheet. Qualify it with a worksheet, such asThisWorkbook.Worksheets("Report").Range("A1"), when the macro must always affect a particular sheet. - The fill seems unchanged: Check for conditional formatting, which may determine the displayed fill. Also confirm that the macro is targeting the intended workbook and worksheet.
- The color is not the shade expected: Check the order of RGB arguments: red first, green second, blue third. Each number must be from 0 through 255.
- The color disappears after clearing: Setting the pattern to
xlNoneremoves the fill; it does not apply white. Use an explicit white RGB fill if you need a white background. - The macro does not run: The workbook must be saved in a macro-enabled format such as
.xlsm, and Excel’s macro security settings must permit the trusted macro to run.
Qualifying the worksheet is a useful habit in production code. For example, with a variable declared as Dim ws As Worksheet and set to Set ws = ThisWorkbook.Worksheets("Report"), use ws.Range("A1").Interior.Color. This avoids relying on whichever sheet happens to be active when the macro runs.
Running VBA on Windows, Mac, and Excel for the web
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, Option+F11 commonly opens it; depending on the keyboard’s function-key settings, you may need Fn+Option+F11. The Developer tab can also open the Visual Basic Editor. If Developer is not visible, enable it in Excel’s ribbon settings.
Excel for Mac supports VBA, though available features and some automation behavior can differ from Windows, especially when code interacts with Windows-specific components. The basic cell formatting properties such as Interior.Color and RGB are standard Excel VBA approaches. Excel for the web does not run VBA macros; use the desktop Excel app to execute this code. A workbook can be stored online, but opening it in a browser does not run its VBA.
Macros can make broad formatting changes quickly, so use a copy of the workbook and run code only when you understand its target range. Do not lower macro security for files from unknown sources. If a workbook came from someone else, verify that its code is trusted before enabling macros.
Frequently Asked Questions
How do I change a cell background color in Excel VBA?
Set the cell’s Interior.Color property, such as Range("A1").Interior.Color = RGB(255, 255, 0) for yellow. Use a qualified worksheet reference if the code must target a specific sheet.
What is the difference between Interior.Color and ColorIndex?Interior.Color accepts a color value, commonly created with RGB, so it is suitable for precise shades. ColorIndex refers to a position in Excel’s indexed color palette and is generally less explicit.
How do I remove a cell background color with VBA?
Set Range("A1").Interior.Pattern = xlNone to remove the fill. This is different from setting a white fill, which remains a cell background color.
Why does my VBA fill not match the color shown in Excel?
A conditional formatting rule may control the visible fill. Check Home > Conditional Formatting and inspect the rules applying to the cell. A theme color or tint can also look different when the workbook theme changes.
Can Excel for the web run VBA macros?
No. Excel for the web can display and edit many workbook features, but it does not execute VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro.
Related
- Excel Shortcut to Insert an Entire Row
- Excel Unmerge Cells Shortcut: Steps, Limits, and Fixes
- Excel Shortcut for Selecting a Column in One Step
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Create a Word Document from a TemplateTo create a Word document from an Excel VBA macro on Windows, use Word’s automation object to open…
- VBA AutoFilter Criteria Not Equal: How to Use ItTo filter out a value with VBA AutoFilter, pass a not-equal criterion such as "<>Closed" to the field…
- Sending Emails With VBA: Excel and Outlook GuideYes—Excel VBA can create and send email through classic Outlook for Windows. In Excel, add a macro that…
- VBA Insert Pivot Table in Excel: Practical Code GuideTo insert a PivotTable with VBA in Excel for Microsoft 365 on Windows, create a PivotCache from your…
- Excel VBA TextBox: Add, Configure, and Use It in FormsAn Excel VBA TextBox is a text-entry control that lets a workbook user type information, such as a…
- Excel VBA Copy Paste Transpose: Working Code and TipsTo copy a range and paste it transposed with Excel VBA, copy the source range and use PasteSpecial…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.