Remove Conditional Formatting but Retain the Effects

Terry has a range of cells that use conditional formatting to shade the cells. He wonders if there is a way to remove the conditional formatting rules for those cells yet still leave the shading that was present before the rules were removed.

From everything we can tell, this must be done using a macro. Here is a simple one that will make the shading static for whatever cells you select:

Sub FreezeFormat1()
    Dim cell As Range

    For Each cell In Selection
        With cell
            If .DisplayFormat.Interior.Color <> vbWhite Then
                .Interior.Color = .DisplayFormat.Interior.Color
            End If
        End With
    Next cell
    Selection.FormatConditions.Delete
End Sub

The macro steps through each selected cell and copies the interior cell color, as displayed, to the actual interior color. It then deletes any conditional formatting rules in the cell. The macro checks to see if the displayed interior cell color is white or not because if it is white, and it is copied, then it makes the cell appear as if the gridlines have been deleted.

Of course, the cell shading could also be due to shading in the cell. The macro can be expanded to freeze the cell pattern, as well:

Sub FreezeFormat2()
    Dim cell As Range

    For Each cell In Selection
        With cell
            .Interior.Pattern = .DisplayFormat.Interior.Pattern
            If .DisplayFormat.Interior.PatternColor <> 0 Then
                .Interior.PatternColor = .DisplayFormat.Interior.PatternColor
            End If

            If .DisplayFormat.Interior.Color <> vbWhite Then
                .Interior.Color = .DisplayFormat.Interior.Color
            End If
        End With
    Next cell
    Selection.FormatConditions.Delete
End Sub

In this iteration of the macro, the pattern color is only frozen if it isn't zero. This is for the same reason that the shading is compared to white—if it isn't done, then it makes the cells look like they have no gridlines.

Finally, you may want to freeze other formatting characteristics, such as font color and attributes. That is done in this iteration of the macro:

Sub FreezeFormat3()
    Dim cell As Range

    For Each cell In Selection
        With cell
            .Font.Color = .DisplayFormat.Font.Color
            .Font.Size = .DisplayFormat.Font.Size
            .Font.Bold = .DisplayFormat.Font.Bold
            .Font.Italic = .DisplayFormat.Font.Italic

            .Interior.Pattern = .DisplayFormat.Interior.Pattern
            If .DisplayFormat.Interior.PatternColor <> 0 Then
                .Interior.PatternColor = .DisplayFormat.Interior.PatternColor
            End If

            If .DisplayFormat.Interior.Color <> vbWhite Then
                .Interior.Color = .DisplayFormat.Interior.Color
            End If
        End With
    Next cell
    Selection.FormatConditions.Delete
End Sub

This version freezes font color, size, bold, and italic attributes. You could easily expand the macro to check and freeze other attributes, as well.

You should note that the above macros will work only if you are using Excel 2010 or later, because it appears that the .DisplayFormat object was added in that version of the program. If you are using an older version of Excel, then see the information provided in this ExcelTip:

https://tips.net/T13721

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10147) applies to Microsoft Excel 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.