Keep Conditional Formatting Colors Without Formulas in Excel
Question details
The user wants to preserve colors produced by conditional formatting without retaining the formulas or rules that generate them.

- 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.
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.
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
In Excel desktop, select one contiguous range containing the displayed colors you want to preserve.
Open the Visual Basic Editor, choose Insert > Module, and paste the macro above into the module.
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.
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.

Reapply the colors manually for a small range
Use this method when there are only a few colors to reproduce or VBA is unavailable.
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.

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.




