Excel VBA: Clear Cell Contents and Fill by Color
Question details
Find Excel cells with a specified fill color, clear their contents, and remove their fill.

- 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.
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.
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.
Activate the worksheet you want to process, then select Developer > Visual Basic. In the editor, select Insert > Module.
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
Return to Excel, select Developer > Macros, choose ClearCellsByFillColor, and click Run. When prompted, select a filled sample cell and click OK.
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.

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.

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.




