In Excel VBA, set a cell’s fill color with its Interior.Color property, usually using RGB(red, green, blue); for example, Range("A1").Interior.Color = RGB(255, 255, 0) fills A1 yellow. To remove a fill, set Interior.Pattern to xlNone. The key distinction is that VBA can set a cell’s stored fill while conditional formatting may control the color you actually see.
What VBA means by cell fill color
A cell’s fill is its background formatting, separate from its value, number format, borders, and font color. In VBA, fill formatting belongs to the cell’s Interior object. For example, Range("A1").Interior.Color reads the fill color, while assigning a color changes it. A range can be one cell, a group of cells, or a whole row or column.
Use a fully qualified range when possible so the code clearly identifies the worksheet. A bare reference such as Range("A1") refers to the active sheet, which may not be the sheet you expect if the user has clicked elsewhere. A reference such as ThisWorkbook.Worksheets("Sales").Range("A1") points to cell A1 on the Sales sheet in the workbook containing the code.
“Fill color” is sometimes confused with font color. The font color property is Font.Color; the background fill is Interior.Color. Changing one does not change the other.
Set a fill with RGB values
RGB(red, green, blue) is a straightforward way to specify a color. Each component is an integer from 0 to 255: 0 means none of that component, and 255 means its maximum. For example, red is RGB(255, 0, 0), white is RGB(255, 255, 255), and black is RGB(0, 0, 0). The same approach works for a custom color, such as a pale blue made with RGB(221, 235, 247).
Before running a macro, save a copy of the workbook: macro changes cannot be undone with Ctrl+Z. This example colors a fixed range on a named worksheet in the workbook containing the code.
Sub ShadeSalesCells()
Dim ws As Worksheet
Dim targetRange As Range
' Point to the worksheet and cells to format.
Set ws = ThisWorkbook.Worksheets("Sales")
Set targetRange = ws.Range("B2:B10")
' Apply a pale blue fill.
targetRange.Interior.Color = RGB(221, 235, 247)
End Sub
Change the sheet name, range, and RGB values to suit the workbook. If the named sheet does not exist in ThisWorkbook, VBA raises an error rather than silently choosing another sheet. If code is stored in a separate macro workbook, ThisWorkbook refers to that macro workbook; you may need to qualify the target workbook differently.
Read a cell’s fill color
To read the color VBA has stored as direct fill formatting, assign Interior.Color to a variable declared as Long. The returned number is an encoded color value, not a friendly color name. You can compare it with another RGB value to test whether it matches a known color.
Save a copy before running macros that make changes, because Ctrl+Z cannot reverse macro actions. The following example reads A1 on the Sales sheet and reports whether its direct fill matches the specified pale blue:
Sub CheckSalesFill()
Dim ws As Worksheet
Dim cellColor As Long
' Read the direct fill color from A1.
Set ws = ThisWorkbook.Worksheets("Sales")
cellColor = ws.Range("A1").Interior.Color
If cellColor = RGB(221, 235, 247) Then
MsgBox "A1 has the pale blue fill."
Else
MsgBox "A1 does not have the pale blue fill."
End If
End Sub
This comparison is useful when automating a workbook with known formatting, but color is not always a good substitute for data. If a cell’s meaning matters to later calculations, consider recording that meaning in a value or separate status column rather than relying only on its appearance.
Color, ColorIndex, and theme colors
VBA provides several ways to specify a fill, and they are not interchangeable in every situation:
Interior.Coloraccepts a color value, commonly produced byRGB(). It is a clear choice for a specific custom color.Interior.ColorIndexuses an index in Excel’s color palette. It can be convenient in older code, but an index is less descriptive than RGB and may not represent the intended color across workbooks with different palettes.Interior.ThemeColorrefers to a workbook theme color, such as an accent color. A theme-based fill can follow theme changes, which is useful when a workbook’s design is meant to adapt as a set.
For example, Interior.ThemeColor = xlThemeColorAccent1 selects the first accent color in the workbook theme. The TintAndShade property can lighten or darken a theme color; its value ranges from -1 to 1, with positive values adding tint and negative values adding shade. RGB is generally easier when you need the same precise color regardless of the theme.
Fill a range or apply colors cell by cell
Assigning a fill to a range applies it to every cell in that range. This is simpler and often faster than looping when all cells should receive the same color. Use a loop when each cell needs a different decision, for example when comparing its value or current fill.
Save a copy before running a formatting macro; its changes cannot be undone with Ctrl+Z. This example colors cells in B2:B20 when their values are below zero:
Sub HighlightNegativeValues()
Dim ws As Worksheet
Dim cell As Range
' Check each value on the named worksheet.
Set ws = ThisWorkbook.Worksheets("Sales")
For Each cell In ws.Range("B2:B20")
If IsNumeric(cell.Value) Then
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 199, 206)
End If
End If
Next cell
End Sub
The two checks prevent a text value from being treated as a negative number. The macro leaves cells with zero, positive numbers, or text unchanged; it does not clear any previous fill from those cells. If you want a complete refresh, decide explicitly what should happen to cells that no longer meet the condition.
Remove a fill without removing other formatting
To clear only the fill, set Interior.Pattern to xlNone. This removes the cell’s fill pattern without clearing its font, borders, or number format. Another commonly used option is setting Interior.ColorIndex to xlColorIndexNone. Avoid clearing all cell formats if you intend to preserve those other properties.
Save a copy before running a macro that changes formatting, since macro actions cannot be undone with Ctrl+Z. This example clears the fill from a range on the Sales sheet:
Sub ClearSalesFill()
Dim ws As Worksheet
Dim targetRange As Range
' Select only the cells whose fills should be removed.
Set ws = ThisWorkbook.Worksheets("Sales")
Set targetRange = ws.Range("B2:B10")
targetRange.Interior.Pattern = xlNone
End Sub
Be precise with the target range: clearing a fill from an entire column may remove useful formatting far beyond the visible data. If the goal is to reset only cells that meet a condition, test each cell before clearing it.
Conditional formatting and the color you see
A frequent source of confusion is that a cell’s direct fill and its displayed fill can differ. Conditional formatting applies a visual format when a rule is true, so the color shown on the sheet may come from a rule rather than from the cell’s ordinary Interior.Color. Setting Interior.Color does not remove or rewrite those rules, and the rule may continue to determine the visible color.
To inspect the color currently displayed after conditional formatting, VBA provides DisplayFormat.Interior.Color. This property is intended for use in a macro or other VBA procedure; it does not work as expected when used in a worksheet user-defined function. If the visible result is wrong, inspect the rules through Home > Conditional Formatting > Manage Rules, check which cells the rules apply to, and review rule order and settings.
When the color should change automatically as values change, conditional formatting is usually a better tool than a macro that paints cells once. In Microsoft 365 for Windows, create or inspect rules from Home > Conditional Formatting. A macro is more appropriate when you need a one-time formatting action, a button-driven process, or a repeatable task that is not naturally expressed as a conditional rule.
Common problems and practical checks
- The wrong sheet changes: A bare
Rangereference uses the active sheet. Qualify it with a worksheet variable or an explicit worksheet reference. - The color appears unchanged: Check for conditional formatting, table styles, or another rule affecting the displayed result. A direct fill assignment does not necessarily override a conditional-formatting display.
- The code errors on a worksheet reference: Confirm the sheet name is spelled exactly as it appears on the tab and that the worksheet belongs to the workbook referenced by
ThisWorkbook. - A color does not match an expected number: The numeric result from
Interior.Coloris an encoded value. Compare it withRGB()rather than assuming it is a simple red, green, or blue component. - The fill is removed but the cell still looks shaded: A conditional-formatting rule or a table style may still supply a visible background.
Also check whether the target range is protected. Worksheet protection can prevent formatting changes depending on how protection was set up. If the macro is intended to format a protected sheet, confirm the workbook’s protection design before changing the code or removing protection.
Running VBA in Windows, Mac, and Excel for the web
In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic if the Developer tab is available. To run a macro, use Alt+F8, select the macro, and choose Run. Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain its macros.
Excel for Mac also supports VBA in the desktop application. The Visual Basic Editor shortcut is commonly Option+F11; some keyboards require holding Fn as well. Ribbon and menu details can differ by Mac version. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel for those tasks.
Frequently Asked Questions
How do I change a cell’s fill color in Excel VBA?
Assign a value to Range("A1").Interior.Color, commonly using RGB(red, green, blue). Qualify the range with its worksheet when you need to target a particular sheet.
How do I get a cell’s fill color in VBA?
Read Range("A1").Interior.Color into a Long variable to get its direct fill color. If conditional formatting affects the visible color, use DisplayFormat.Interior.Color in a macro to inspect the displayed result.
How do I remove a cell fill but keep its borders?
Set Range("A1").Interior.Pattern = xlNone. That clears the fill pattern without clearing borders, font formatting, or number formats.
Why does VBA set a color that I cannot see?
A conditional-formatting rule or table style may determine the displayed background. Inspect the rules and the range they apply to; assigning a direct fill does not necessarily change the color shown by a conditional-formatting rule.
Can Excel for the web run a VBA macro?
No. Excel for the web can open a macro-enabled workbook, but VBA macros must be created, edited, and run in desktop Excel for Windows or Mac.
Related
- Excel Shopping List Template: Create, Organize, and Print
- Excel: Shade the Area Under a Curve
- Excel Savings Plan Template: Build a Monthly Goal Tracker
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA AutoFilter Field Criteria1: Values, Operators & ExamplesIn Excel VBA, Field identifies which column within the filtered range to filter, while Criteria1 supplies the first…
- Excel VBA: Find the Last Row of Data in a Column EasilyTo find the last populated row in column A with Excel VBA, use Cells(Rows.Count, "A").End(xlUp).Row, with the worksheet…
- VBA Loop Example: For, Do While, and For Each LoopsA VBA loop repeats a block of code, either a set number of times or until a condition…
- VBA Copy and Paste Worksheet: Practical ExamplesTo copy an entire worksheet in Excel VBA, use the worksheet’s Copy method and specify where the copy…
- VBA Open Excel File and Save as CSV with Working CodeYes. In VBA, open the workbook with Workbooks.Open, then call SaveAs with FileFormat:=xlCSVUTF8 to create a UTF-8 CSV…
- MS Access VBA MsgBox Examples: Syntax, Buttons and UseIn Microsoft Access VBA, use MsgBox to show a message or ask a user a question; combine button…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.