To clear a cell’s fill color with VBA, set its Interior.Pattern to xlPatternNone, such as Range("A1").Interior.Pattern = xlPatternNone. For a range, use the same property on the whole range; this removes the fill without deleting the cell’s values, formulas, borders, or number formats.
The basic VBA method
Excel stores a cell’s fill as part of its Interior formatting. The Pattern property determines whether that interior has a fill pattern. Setting it to xlPatternNone removes the fill rather than replacing it with a different color. A small macro for one cell looks like this:
Sub ClearOneCellFill()
Dim target As Range
' Change B2 to the cell you want to clear.
Set target = ActiveSheet.Range("B2")
target.Interior.Pattern = xlPatternNone
End Sub
This targets cell B2 on the active worksheet. If the macro is run while a different sheet is active, it affects B2 on that sheet instead. For repeatable work, it is safer to name the worksheet explicitly, as in the examples below.
You will also see Interior.ColorIndex = xlColorIndexNone used to remove a fill. That is a valid way to specify that the interior has no color. The pattern assignment is a clear, direct choice when the goal is to remove the fill, including a patterned fill. You can set both properties if you want the code to state explicitly that there is neither a pattern nor a color.
Clear a range, row, column, or worksheet
The Interior property can be applied to a multi-cell range. Excel changes the fill for every cell in that range, while leaving other formatting in place. This example clears a block on a named sheet:
Sub ClearReportRangeFill()
Dim target As Range
' ThisWorkbook means the workbook that contains this macro.
Set target = ThisWorkbook.Worksheets("Report").Range("B2:D20")
target.Interior.Pattern = xlPatternNone
End Sub
Replace Report with the worksheet’s tab name and adjust the range to fit your data. The tab name must match, including spaces. For example, a sheet named “Monthly Report” would be referenced as Worksheets("Monthly Report"). If the macro is stored in a different workbook, remember that ThisWorkbook refers to the workbook containing the code, not necessarily the workbook currently selected on screen.
You can use the same approach for a whole row or column. For example, Range("A:A").Interior.Pattern = xlPatternNone clears fill in column A. To clear fills throughout a worksheet, you can apply the property to Cells, but that covers every cell, not just those containing data. A smaller target such as a known range or the sheet’s UsedRange is usually more efficient and makes the intended scope easier to check.
Clear fill from the current selection
If you select cells before running a macro, you can apply the change to that selection. This is handy for a one-off cleanup, but it depends on the correct worksheet and cells being selected. The following version checks that the selection is a range before changing it:
Sub ClearSelectedCellsFill()
Dim target As Range
' A selection can be something other than a cell range.
If TypeName(Selection) <> "Range" Then Exit Sub
Set target = Selection
target.Interior.Pattern = xlPatternNone
End Sub
For a macro you plan to reuse, a fixed worksheet and range are generally less error-prone than Selection. A selection-based macro can affect more cells than expected if the user has entire rows or columns selected. It can also stop without doing anything if the active selection is an object, such as a chart.
Choose the right kind of formatting change
“Clear the fill” is not the same as “make the cell white.” These approaches can look similar on a plain white worksheet, but they behave differently:
- Remove the fill:
Interior.Pattern = xlPatternNoneleaves the cell with no direct fill. This is usually what you want when cleaning formatting. - Set a white fill: assigning
Interior.Color = vbWhitepaints the cell white. The cell still has a fill, which can hide a colored background behind it or behave differently if the sheet’s appearance changes. - Remove all formatting:
ClearFormatsremoves much more than fill, potentially including fonts, borders, alignment, and number formats. Use it only when you really intend to reset all formatting.
For instance, a cell may show a date as a date because it has a date number format. Clearing its fill with Interior.Pattern = xlPatternNone preserves that display. Clearing all formats may remove the date format and make the underlying serial number visible instead. Keeping the operation narrow is especially important in reports and templates where other formatting carries meaning.
Conditional formatting and other apparent fills
A cell can appear colored even after its direct fill has been cleared. Conditional formatting can apply a color when a rule’s conditions are met, and Excel displays that formatting over the cell’s ordinary appearance. In that situation, VBA may successfully remove the direct fill while the conditional color remains visible.
To investigate, select the affected cell and use Home > Conditional Formatting > Manage Rules. Check whether a rule applies to the cell and whether its condition is currently true. You can edit the rule, change its format, or remove it if it is no longer needed. Be cautious with Clear Rules: the menu offers options for selected cells or the entire sheet, and removing a rule may affect many cells.
VBA can delete conditional-formatting rules from a range with its FormatConditions.Delete method, but that removes the rules, not just their fill color. It can also remove rules that control font color, borders, or other formats. If a rule is responsible for the visible fill, decide whether to edit that rule or delete it before using such code. A table style can also supply banded or emphasized colors; changing a cell’s direct interior may not remove the style’s visual effect. In that case, inspect the table’s style and style options rather than repeatedly clearing the cell fill.
Common errors and how to avoid them
When a macro appears not to work, the cause is often its target or another formatting layer rather than the property itself. Check these points before broadening the code:
- Wrong active sheet: code using
ActiveSheetworks on whichever worksheet is active when it runs. UseThisWorkbook.Worksheets("Report")when the intended sheet is known. - Misspelled sheet name: the name inside
Worksheets("...")must match the worksheet tab. A wrong name commonly causes a subscript error. - Fill still appears: look for conditional formatting or a table style; the visible color may not be a direct cell fill.
- Too much formatting disappeared: check for
ClearFormatsin the code. That method is broader than changingInterior.Pattern. - Only some cells changed: confirm the address includes every intended cell and that merged cells or selection boundaries are not affecting the target.
When debugging, test on a small, known range first. You can also select a cell and inspect its formatting manually, but remember that conditional formatting can make the displayed appearance differ from the underlying direct fill.
Run the macro safely in Excel
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure into the module, and return to Excel. You can run it from the editor with the cursor inside the procedure, or run a listed macro using Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
On a Mac, open the Visual Basic Editor from the Developer tab; depending on the keyboard and function-key settings, the shortcut may be Option+F11 or Fn+Option+F11. The Developer tab can be enabled in Excel’s ribbon and toolbar settings. Excel for the web can open a macro-enabled workbook, but it does not run VBA macros; use the desktop Excel application to execute this code.
Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z on Windows or Command+Z on Mac. To retain VBA code in the file, save it in a macro-enabled Excel workbook (.xlsm) and follow your organization’s macro-security rules. If the workbook is saved as a standard .xlsx file, VBA code is not retained.
Frequently Asked Questions
What is the simplest VBA line to remove a cell’s fill?
Use Range("A1").Interior.Pattern = xlPatternNone, replacing A1 with the target address. To clear a block, use the block’s address instead, such as Range("A1:C10").
Does clearing the fill delete cell contents?
No. Changing Interior.Pattern affects the cell’s fill formatting, not its value or formula. It also leaves unrelated formatting, such as borders and number formats, in place.
Why is the cell still colored after the macro runs?
Conditional formatting or a table style may be supplying the visible color. Check the conditional-formatting rules for the cell and the table’s style before assuming the direct fill was not removed.
Should I use white or no fill?
Use no fill when you want to remove the cell’s direct background formatting. Use a white fill only when you specifically want to paint the cell white; it is not equivalent to having no fill.
Can I undo a VBA fill change with Ctrl+Z?
No. Running a VBA macro clears Excel’s ordinary undo history for that operation, so Ctrl+Z cannot restore the change. Save a separate copy first, especially before running code across a large range or worksheet.
Related
- Excel Redo Shortcut: Repeat Your Last Action on Windows
- Excel Shortcut to Select Every Cell in a Column on Windows
- Excel Shortcut for Lowercase Text: What Actually Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA InputBox Function: Syntax and ExamplesThe Excel VBA InputBox function displays a dialog that asks a user for a response and returns that…
- VBA MsgBox OK Only: Syntax and Button OptionsYes. In Excel VBA, MsgBox displays an OK-only dialog by default, and you can make that choice explicit…
- Excel VBA: How to Hide a Workbook WindowTo hide the active workbook window with VBA, set its Visible property to False, for example: ThisWorkbook.Windows(1).Visible =…
- How to Concatenate Strings in VBA Using & and JoinIn VBA, concatenate strings by joining them with the ampersand operator (&), such as fullName = firstName &…
- Excel VBA: Hide a Column by Number in Your WorksheetTo hide a column by its number in Excel VBA, set that worksheet column’s Hidden property to True.…
- Excel VBA AutoFilter: Copy Visible Results to Another SheetYes. In Excel for Microsoft 365 on Windows, apply AutoFilter to the source range, then copy its visible…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.