To set a cell’s background color in Excel VBA, assign a color to its Interior.Color property—for example, Range("B2").Interior.Color = RGB(255, 255, 0) makes B2 yellow. The example below shows the complete macro; use the worksheet and cell address that match your workbook.
A basic macro for coloring one cell
In Microsoft Excel for Microsoft 365 on Windows, open the Visual Basic Editor, insert a standard module, and add this procedure. It assumes that the code is stored in the workbook you want to change and that the workbook has a worksheet named Sheet1.
Sub SetCellBackground()
Dim target As Range
' Address the cell on the specified worksheet.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2")
' Apply a yellow fill using red, green, and blue values.
target.Interior.Color = RGB(255, 255, 0)
End Sub
Run the macro, and B2 receives a solid yellow background. Change "Sheet1" to the sheet’s actual tab name and "B2" to the target cell. The worksheet name must match, including spaces; for example, use Worksheets("Quarter 1") for a tab called Quarter 1.
Before running any macro that changes a workbook, save a separate copy. VBA changes generally cannot be reversed with Ctrl+Z, so a backup is the straightforward way to protect the original. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep its code.
What Interior.Color means
Excel stores a cell’s fill settings in its Interior object. The Color property sets the fill color, while other properties control related features such as the pattern and theme tint. The RGB function is a convenient way to specify a color by its red, green, and blue components.
Each component ranges from 0 to 255. An all-zero combination is black, all three values at 255 produce white, and equal values produce shades of gray. For instance, RGB(0, 0, 255) is blue, and RGB(255, 165, 0) is orange. This is an RGB color value, not a worksheet formula: it belongs in VBA code, not in a cell.
Use Interior.Color when you want a specific color that does not depend on the workbook’s current theme. If you want the fill to follow a theme color, use theme-related properties instead. A theme-based color can adapt when the workbook theme changes; a fixed RGB value normally retains the specified color.
Color a range or use row and column numbers
The same property works on a multi-cell range. Every cell in the specified range gets the fill, which is useful for highlighting a heading or a fixed block of cells.
Sub SetRangeBackground()
Dim target As Range
' Set the fill for a block on the specified worksheet.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1:D1")
target.Interior.Color = RGB(217, 225, 242)
End Sub
For a cell identified by row and column numbers, use the Cells property. For example, row 2 and column 2 identify B2. This can be useful when a loop calculates which cell to format, rather than assembling an address as text.
Sub ColorCellByPosition()
Dim target As Range
' Cells uses row number first, then column number.
Set target = ThisWorkbook.Worksheets("Sheet1").Cells(2, 2)
target.Interior.Color = RGB(255, 255, 0)
End Sub
Using a fully qualified reference—worksheet and range together—helps avoid coloring the wrong cell when another sheet is active. A bare reference such as Range("B2") can refer to the active sheet, making the result depend on which sheet is selected when the macro runs.
Choose RGB, a color index, or a theme color
There are several ways to specify a fill, but they suit different needs:
Interior.Color = RGB(red, green, blue)is a clear choice for a precise, reusable color. The component values make it easy to read and adjust.Interior.ColorIndexuses Excel’s indexed palette. It can be useful in older code or when working with a limited palette, but an index is less descriptive than an RGB value and may not match a desired color as directly.Interior.ThemeColor, often used withInterior.TintAndShade, selects a workbook theme color and a variation. Choose this approach when the fill should fit the workbook’s theme.
For most new macros that need a particular color, RGB is straightforward. The values are explicit, and you do not have to look up what an index or theme slot represents. Conversely, if a workbook is designed around theme colors, a fixed RGB fill may look out of place after someone changes the theme.
Color cells based on their contents
A macro can inspect a value before applying a fill. This example colors cells in B2:B20 yellow when their value is less than zero. It does not color blank cells, and it assumes the worksheet exists under the stated name.
Sub HighlightNegativeValues()
Dim ws As Worksheet
Dim cell As Range
' Check each value in the specified range.
Set ws = ThisWorkbook.Worksheets("Sheet1")
For Each cell In ws.Range("B2:B20")
If IsNumeric(cell.Value) And cell.Value < 0 Then
cell.Interior.Color = RGB(255, 199, 206)
End If
Next cell
End Sub
In this example, the pale red fill is assigned only when the condition is true. Cells that do not meet the condition keep whatever formatting they already have. If you also want to remove an old fill when a value is no longer negative, add an Else branch that resets the cell’s fill. Think through that behavior first: clearing a fill may remove intentional formatting that was already present.
For highlighting that should update automatically as values change, Excel’s conditional formatting is often more suitable than a one-time macro. On Windows, use Home > Conditional Formatting and choose a rule that fits the data. A macro applies formatting when it runs; conditional formatting recalculates its display based on its rules and cell values.
Remove a fill or change its pattern
To remove a cell’s fill, clear the interior pattern rather than assigning white. White is still a fill color, while no fill lets the cell display the worksheet background. This distinction matters when a sheet uses a colored background or when you want the cell to blend with unfilled cells.
Sub RemoveCellFill()
Dim target As Range
' Clear the fill pattern from the selected cell.
Set target = ThisWorkbook.Worksheets("Sheet1").Range("B2")
target.Interior.Pattern = xlNone
End Sub
If you need to restore a particular fill later, assign its color again. Excel does not infer the cell’s former color after it has been replaced. Also, a cell can have a pattern as well as a color; if the appearance is not what you expect, inspect both the pattern and the fill settings rather than changing only one property.
Run the macro safely on Windows or Mac
On Windows, open the Visual Basic Editor with Alt+F11. To insert a module, choose Insert > Module in the editor, paste the code, and run the procedure from the editor or with Alt+F8 from Excel. If the Developer tab is already visible, its Developer > Visual Basic command also opens the editor.
On Mac, Excel supports VBA, though the interface and keyboard shortcuts can differ. Option+F11 opens the Visual Basic Editor on many Mac keyboards; depending on the keyboard’s function-key settings, you may need Fn as well. You can also use the Visual Basic command on the Developer tab. Macro availability and security settings affect whether code can run, so use a trusted workbook and follow your organization’s security rules.
Excel for the web does not run VBA macros. Open the workbook in the desktop Excel app to use this code. Before running it, confirm that the macro points to the intended workbook, worksheet, and range. A typo in a sheet name can produce an error, while an unqualified range can act on an unintended active sheet.
Common problems and how to diagnose them
- The macro reports a subscript error: Check that the worksheet name in
Worksheets("Sheet1")matches the tab exactly and that the reference points to the intended workbook. - The color appears unchanged: Check whether conditional formatting is displaying a different fill. A conditional-formatting rule can affect the visible appearance even when VBA has changed the cell’s underlying direct formatting.
- The macro colors the wrong sheet: Qualify the range with its worksheet, as in
ThisWorkbook.Worksheets("Sheet1").Range("B2"), rather than relying on the active sheet. - The macro disappears after saving: Save the file as
.xlsm, not as a macro-free workbook format that removes VBA code. - The fill is white rather than absent: White is a color. To remove the fill, use
Interior.Pattern = xlNone.
One common misconception is that assigning a color changes the cell’s value. It does not: Interior.Color changes formatting only. The cell’s contents and formulas remain separate from its fill, unless other code explicitly changes them.
Frequently Asked Questions
How do I set a cell background color using VBA?
Assign an RGB color to the cell’s interior, such as Range("B2").Interior.Color = RGB(255, 255, 0). In a macro, qualify the range with its worksheet to avoid relying on the active sheet.
What is the VBA code for a red cell?
For a bright red fill, use target.Interior.Color = RGB(255, 0, 0), after setting target to the cell or range you want to format.
How do I color several cells at once?
Set the color on a range such as Worksheets("Sheet1").Range("A1:D1").Interior.Color. Every cell in that range receives the same fill.
How do I remove a cell background color in VBA?
Use target.Interior.Pattern = xlNone to remove the fill pattern. Setting the color to white is different because white remains a fill.
Why does my VBA fill not show in Excel for the web?
Excel for the web does not run VBA macros. Run the macro in desktop Excel, then save the workbook; the formatting can be viewed in the browser, but the VBA code itself requires desktop Excel to execute.
Related
- Excel Shortcut to Select the Rest of a Column Down
- Excel Shortcut to Select an Entire Column
- Excel Shortcut to Select to the End of a Column Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Py Macro Recorder: How Python Records and Replays ActionsA Py macro recorder is a tool for capturing keyboard or mouse actions and replaying them, often with…
- Excel VBA InputBox Validation: A Practical GuideTo validate an Excel VBA input, use Application.InputBox to collect a value, check it against your rules in…
- Excel VBA GoTo: How Labels and Errors WorkIn Excel VBA, GoTo is a statement—not a function—that transfers execution to a labeled line in the same…
- LEFT Excel Macro: Formula and VBA ExplainedLEFT is not an Excel macro: it is a worksheet function that returns a specified number of characters…
- Excel VBA Or Operator: How It Works and When to Use ItIn Excel VBA, the Or operator combines conditions and returns True when at least one condition is True.…
- Excel VBA to Open Another Workbook: Code and TipsTo open another Excel file with VBA, call Workbooks.Open and supply the file’s full path. For example, Set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.