Formatting Issues

Keep Conditional Formatting Colors Without Formulas in Excel

Adam DavisAdam Davis Nov 14, 2026 868 views

Question details

The user wants to preserve colors produced by conditional formatting without retaining the formulas or rules that generate them.

Keep Conditional Formatting Colors Without Formulas in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Create a static snapshot of conditionally formatted cells before removing formulas or their source data.
Observed behavior
Conditional formatting colors can disappear when the rules or their source formulas and values are removed.
Before you start

Identify the contiguous range whose displayed fill colors you want to keep. If the colors must continue to change automatically, retain the conditional formatting rules and their source data instead.

Solution 1Recommended

Convert displayed conditional colors to static fills with VBA

Use Excel desktop and VBA to capture displayed fill colors across a selected range before deleting its conditional formatting rules.

This macro preserves displayed fill colors, not conditional font colors, borders, gradients, or other effects. It replaces nonempty fill patterns with solid fills.

Sub KeepDisplayedFills() Dim r As Range, c As Range If TypeName(Selection) <> "Range" Then Exit Sub Set r = Selection If r.Areas.Count > 1 Then MsgBox "Select one contiguous range." Exit Sub End If For Each c In r.Cells If c.DisplayFormat.Interior.Pattern = xlNone Then c.Interior.Pattern = xlNone Else c.Interior.Pattern = xlSolid c.Interior.Color = c.DisplayFormat.Interior.Color End If Next c r.FormatConditions.Delete End Sub

1
Select the cells

In Excel desktop, select one contiguous range containing the displayed colors you want to preserve.

2
Add the macro

Open the Visual Basic Editor, choose Insert > Module, and paste the macro above into the module.

3
Run the macro

Return to the worksheet, keep the range selected, and use Developer > Macros to run KeepDisplayedFills. The macro applies regular fills and deletes conditional formatting rules from the selected cells.

4
Replace formulas if needed

With the range selected, copy it and choose Home > Paste > Paste Special > Values. This replaces formulas in the selected cells with their current results while retaining the static fills.

Convert displayed conditional colors to static fills with VBA
Rules no longer update: The selected cells will keep static fills, but their colors will no longer respond to data changes. Replacing formulas with values also stops those formulas from recalculating.
Free Microsoft Office alternative

Work with formatted spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Excel format compatibility for an easier move between office suites. It does not perform the Excel VBA conversion described above.

Apply cell fill colors and conditional formatting in WPS SpreadsheetsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar interface for everyday spreadsheet tasksMove existing spreadsheets to a lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Will pasting values alone preserve conditional formatting colors?

Not reliably. Paste Special > Values removes formulas from the pasted cells, but conditional colors can change if the rules lose the data they depend on. Convert the displayed colors to regular fills first.

Can the colors still change after I convert them to static fills?

No. Regular fills stay fixed until you change them. To keep automatic color changes, retain the rules under Home > Conditional Formatting and keep their source data or formulas.

Does the VBA macro preserve font colors and borders too?

No. The macro captures displayed cell fill colors only. It does not preserve conditional font colors, borders, gradients, or other effects.

What happens when I paste values over formulas?

Excel replaces each formula with its current result, so it will no longer recalculate. Select the cells, copy them, and choose Home > Paste > Paste Special > Values only when you no longer need those formulas.