VBA & Macro Problems

Excel VBA: Clear Cell Contents and Fill by Color

Steven ParkerSteven Parker Nov 20, 2026 869 views

Question details

Find Excel cells with a specified fill color, clear their contents, and remove their fill.

Clear Excel Cell Contents and Fill by Color with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to clear values and fill from cells of a selected interior color using VBA.
Observed behavior
A direct formatted replacement does not clear the cell values.
Before you start

Open the workbook in desktop Excel and identify a cell with the fill color you want to match. Unmerge cells in the worksheet's used range before running the macro, and note that matching formulas will also be deleted.

Solution 1Recommended

Clear matching cells with a VBA loop

Use a sample cell to select the target fill color, then clear contents and remove the fill from matching cells in the active worksheet's used range.

This macro checks the stored cell fill, including fills on empty cells. It does not match colors displayed only by conditional formatting, and it leaves other formatting unchanged.

1
Open the VBA editor

Activate the worksheet you want to process, then select Developer > Visual Basic. In the editor, select Insert > Module.

2
Add the macro

Paste this code into the module: Sub ClearCellsByFillColor() Dim ws As Worksheet Dim sample As Range Dim cell As Range Dim targetColor As Long Set ws = ActiveSheet On Error Resume Next Set sample = Application.InputBox("Select a cell with the fill color to clear:", Type:=8) On Error GoTo 0 If sample Is Nothing Then Exit Sub Set sample = sample.Cells(1, 1) If sample.Interior.ColorIndex = xlColorIndexNone Then MsgBox "Select a cell with a fill color." Exit Sub End If targetColor = sample.Interior.Color For Each cell In ws.UsedRange.Cells If cell.Interior.ColorIndex <> xlColorIndexNone Then If cell.Interior.Color = targetColor Then cell.ClearContents cell.Interior.ColorIndex = xlColorIndexNone End If End If Next cell End Sub

3
Run and select a color

Return to Excel, select Developer > Macros, choose ClearCellsByFillColor, and click Run. When prompted, select a filled sample cell and click OK.

4
Check the results

Review the active worksheet to confirm that cells with the matching stored fill have no contents or fill. The sample cell is also cleared if it is in that worksheet's used range.

Clear matching cells with a VBA loop
Scope and limitations: The macro processes the active worksheet's used range and may take time on a large range. It removes matching values and formulas, so verify the target worksheet and color before running it.
Free Microsoft Office alternative

Work with Color-Formatted Spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel workbooks and manage cell contents and formatting; switching to WPS does not fix an Excel VBA macro.

Edit spreadsheet cell contents and fill colorsOpen and save common Microsoft Excel formats such as XLSXUse a familiar interface for a smoother move from OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro delete formulas as well as values?

Yes. The ClearContents command removes both formulas and values from matching cells. Check the target worksheet and fill color before running the macro.

Will this remove borders or number formatting?

No. ClearContents removes cell contents, and Interior.ColorIndex = xlColorIndexNone removes the fill. Other formatting, such as borders and number formats, remains.

Can the macro find colors applied by conditional formatting?

No. It checks the cell's stored Interior color, not a color displayed only by conditional formatting. Review the rules under Home > Conditional Formatting if the displayed color comes from a rule.

How can I limit the macro to a specific range?

In the VBA editor, replace ws.UsedRange.Cells in the For Each line with a range such as ws.Range("A1:D100"). Run the macro again through Developer > Macros.