To format a cell’s fill color with Excel VBA, set its Interior.Color property; for example, Range("A1").Interior.Color = RGB(255, 0, 0) makes A1’s fill red. To change the text color instead, use Range("A1").Font.Color. The examples below work in desktop Excel for Microsoft 365 on Windows and also cover ranges, theme colors, conditional formatting, and common problems.
Basic VBA code for a cell fill
The simplest approach is to refer to a cell or range directly and assign a color. This macro colors B2:B20 a pale yellow and changes the text to dark blue:
Option Explicit
Sub ColorStatusCells()
Dim ws As Worksheet
Dim target As Range
' Use the active worksheet and the status cells.
Set ws = ActiveSheet
Set target = ws.Range("B2:B20")
' Set a solid fill, then set the text color.
target.Interior.Pattern = xlSolid
target.Interior.Color = RGB(255, 230, 153)
target.Font.Color = RGB(31, 78, 121)
End Sub
Change the worksheet reference or address to suit your workbook. For a fixed worksheet, use Set ws = ThisWorkbook.Worksheets("Summary") instead of ActiveSheet. That helps avoid coloring the wrong sheet if a different tab happens to be selected when the macro runs. A range assignment applies the same fill to every cell in that range; it does not examine each cell’s value.
How Interior.Color and Font.Color work
Excel exposes cell formatting through different properties. Interior controls the cell’s fill, while Font controls text formatting. Their Color properties accept a VBA color value, commonly returned by the RGB function. The arguments are red, green, and blue intensity values, each from 0 to 255. For example, RGB(0, 0, 0) is black, RGB(255, 255, 255) is white, and RGB(0, 128, 0) is green.
To set a font color without changing the fill, assign only the font property. To set a fill without affecting the font, assign only the interior property. Borders are separate again: their color is set through a border object, so changing Interior.Color does not color a cell’s outline. This separation matters when a macro should change only one part of the appearance and leave existing formatting intact.
Excel’s VBA color properties use a numeric color value represented as a Long. Using RGB is easier and less error-prone than trying to calculate that number manually. Although the internal representation may look different from a familiar hexadecimal web color, the VBA function takes red, green, and blue in the normal order.
Choosing RGB, ColorIndex, or a theme color
There are several ways to specify a color, and the right choice depends on whether you want a precise shade or a color that follows the workbook’s theme.
- RGB: Use
Interior.Color = RGB(255, 0, 0)when you want a specific, repeatable red regardless of the workbook theme. RGB is usually the clearest choice for a fixed color palette. - ThemeColor: Use a theme color when the fill should track the workbook’s design theme. For example,
Interior.ThemeColor = xlThemeColorAccent1selects the first accent color. The displayed shade can change if someone changes the workbook theme. - ColorIndex: This property selects a color by its index in Excel’s color palette. It is mainly useful when maintaining older workbooks or code that already uses palette indices. It is less descriptive than RGB and may not express the exact color you intend in all contexts.
Theme colors can also be lightened or darkened with the TintAndShade property, which takes a value from -1 to 1. A value of 0 leaves the theme color untinted; positive and negative values adjust its shade. For example, a theme-based fill can be set by assigning Interior.ThemeColor and then setting Interior.TintAndShade. Use theme properties when consistency with a document’s changing theme is more important than preserving one exact RGB shade.
Color a range based on its values
A fixed assignment colors every cell in the target range alike. If the goal is to color cells differently according to their values, loop through the cells and apply a condition. This example colors values below zero in a selected range:
Option Explicit
Sub MarkNegativeValues()
Dim cell As Range
Dim target As Range
Set target = ActiveSheet.Range("C2:C50")
For Each cell In target.Cells
If IsNumeric(cell.Value) And cell.Value < 0 Then
cell.Interior.Color = RGB(255, 199, 206)
End If
Next cell
End Sub
The macro checks each cell in C2:C50 and applies a pale red fill only when its value is numeric and less than zero. Other cells are left as they are; they are not automatically cleared or recolored. This is useful when you want a one-time formatting pass or when a macro needs to apply more complicated rules than a standard conditional format can express.
For a rule that should update automatically when cell values change, Excel’s conditional formatting is often more appropriate. On Windows, use Home > Conditional Formatting to create a rule, such as highlighting values below zero. VBA-applied fill is direct formatting; conditional formatting is a rule-based display layer. A conditional-formatting rule can determine the visible color even if the cell also has a normal fill. If the VBA fill seems not to appear, inspect the cell’s rules through Home > Conditional Formatting > Manage Rules before assuming the macro failed.
Change or remove a cell fill
To replace an existing fill, assign a new color to Interior.Color. To remove the fill pattern, set Interior.Pattern to xlNone. For example, the following statement clears the direct fill from A1:
Range("A1").Interior.Pattern = xlNone
Clearing a direct fill is not the same as deleting every kind of color rule. Conditional formatting may still display a color, and font or border formatting remains unchanged. Conversely, clearing formats from a cell through Excel’s interface can remove more than just the fill, depending on the command used. Be precise about whether you intend to clear only the interior, remove a conditional-formatting rule, or reset broader cell formatting.
If you want to clear the fill from a larger area, replace A1 with a range such as Range("A2:D100"). Avoid clearing an entire worksheet unless that is genuinely intended, since it can remove useful formatting over a much larger area than expected.
Run the macro and save the workbook safely
In Windows desktop Excel, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure into it. In the editor, use Insert > Module if a module is not already present. Return to Excel and run the macro from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.
On a Mac, VBA is available in desktop Excel, though some keyboard shortcuts and editor details vary by keyboard and version. Use the Developer tab’s Visual Basic and Macros commands if available, or find the editor through Excel’s macro tools. Excel for the web does not run VBA macros; open the workbook in desktop Excel to execute this code. Saving as an Excel Macro-Enabled Workbook (.xlsm) preserves VBA. Saving as a regular .xlsx file does not retain VBA code.
Before running a macro that changes formatting, save a copy of the workbook. Macro changes cannot be undone with Ctrl+Z, so a backup is the dependable way to recover if the code affects the wrong range. Also enable macros only in workbooks you trust; macros can do more than change colors.
Common problems and how to fix them
- The color does not appear: Check that the macro ran without an error, that the range address points to the intended sheet, and that a conditional-formatting rule is not overriding the visible fill.
- The wrong sheet changes:
ActiveSheetmeans whichever worksheet is active at run time. Refer to a named worksheet throughThisWorkbook.Worksheetswhen the macro should always target one particular sheet. - The fill looks different from the expected shade: Confirm the RGB argument order and values. Each component must be between 0 and 255. Also check whether a theme color, conditional format, or transparency-like pattern setting affects what you see.
- The macro is unavailable: Confirm that the workbook is saved as
.xlsm, that the code is in a standard module, and that macros are permitted by the workbook’s security settings. Excel for the web cannot execute VBA. - Only some cells change: A loop with an If condition intentionally skips cells that do not meet the condition. Review the range and comparison, and decide whether skipped cells should keep their old fill or have it cleared.
Frequently Asked Questions
How do I change a cell’s background color with VBA?
Set the cell’s Interior.Color property, such as Range("A1").Interior.Color = RGB(255, 255, 0) for yellow. Use Interior.Pattern = xlSolid if you also want to explicitly set a solid fill.
How do I change font color instead of fill color?
Use the cell’s Font.Color property. For example, Range("A1").Font.Color = RGB(0, 0, 255) sets the text to blue without changing the cell background.
Can VBA color a cell based on its value?
Yes. Use a loop and an If condition to check each cell, or create a conditional-formatting rule. Conditional formatting is usually preferable when the color must update automatically as values change.
Why does my VBA fill color not show?
A conditional-formatting rule may control the visible appearance, or the code may be targeting a different sheet or range. Check the address and inspect the cell’s rules under Home > Conditional Formatting > Manage Rules.
Can I run Excel VBA color macros in a browser?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac, and save it as an .xlsm file if it needs to retain the macro.
Related
- Excel Shortcut to Select to the Bottom of a Column
- Excel Shortcut to Select All Rows in a Column at Once
- Excel Data Validation from a List: Setup and Troubleshooting
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Find and Replace in a Range: Code and TipsTo find and replace text only within a chosen Excel range, use VBA’s Range.Replace method and call it…
- VBA Function Return Multiple Values: What WorksYes. A VBA function can provide multiple results, but it has only one formal return value. To deliver…
- Excel Macro Error? Common Causes and FixesAn Excel macro error usually means VBA is blocked, the code cannot find an expected workbook, worksheet, or…
- Excel VBA Worksheet Attributes: Properties and ExamplesIn Excel VBA, “worksheet attributes” usually means the properties of a Worksheet object: values that describe or control…
- Excel VBA MsgBox New Line: Add Line Breaks CorrectlyTo put a new line in an Excel VBA message box, join the text with vbCrLf: MsgBox "First…
- Excel VBA Procedure Too Large Error: Causes and FixesThe Excel VBA “Procedure too large” error means a single Sub or Function has grown beyond what the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.