How to Remove Conditional Formatting but Keep the Format in Excel

If we remove conditional formatting from cells where it has been applied, Excel also removes the cell formats. In this article, we will demonstrate 2 different ways to remove conditional formatting but keep the cell formats.

To illustrate our methods, we’ll use the dataset below, which contains a sale list including the product name, unit price, and sale quantity. We’ll first highlight the products that have a sale quantity of more than 50 using conditional formatting, then remove this formatting while preserving the general cell format.

To add the conditional formatting:

  • Select the quantity column (cells D5:D14).
  • From the Home tab on the ribbon, click Conditional Formatting.
  • From the dropdown, choose Highlight Cells Rules.
  • Then choose the Greater Than option.

Remove Conditional Formatting but Keep the Format

  • In the Greater Than window, enter 50 and click OK.

The cells having quantity greater than 50 now have a red background.

  • To check the conditional formatting, change any cell value in the Quantity column that is less than 50 to a value greater than 50.

The following screenshot shows how the background color of cell D7 turns red when its value changed from 17 to 56.

Remove Conditional Formatting but Keep the Format

Remove Conditional Formatting and Keep the Format

Now we want to remove the conditional formatting rules while keeping the format at the same time. Using the standard procedure to remove conditional formatting rules, general formatting will also be removed.

Remove Conditional Formatting but Keep the Format

Let’s remove the formatting rules but keep the formats.


Method 1 – Using the Office Clipboard

Steps:

  • Select the whole dataset.
  • Press Ctrl + C to copy the selected cells.

Remove Conditional Formatting but Keep the Format

  • Select the cell to paste the copied cells.
  • From the Home tab, click on the Clipboard icon.

Remove Conditional Formatting but Keep the Format

  • Click on the copied item on the Clipboard panel.

Remove Conditional Formatting but Keep the Format

The copied cells are pasted at the specified location.

  • Close the Clipboard panel.

Remove Conditional Formatting but Keep the Format

  • Delete the old dataset and reposition the new one.

Now if we change the value of a cell in the Quantity column to greater than 50, the format doesn’t change.

Remove Conditional Formatting but Keep the Format

Read More: How to Copy Conditional Formatting to Another Sheet


Method 2 – Running a VBA Code

Using VBA code, we can also remove conditional formatting while preserving the cell format.

Steps:

  • Select the cells that are conditionally formatted (in our dataset, cells D5:D14).

Remove Conditional Formatting but Keep the Format

  • Go to the Developer tab on the Excel Ribbon.
  • Click the Visual Basic option.

  • In the Visual Basic for Applications window, click the Insert dropdown to open a new Module.

Remove Conditional Formatting but Keep the Format

  • Copy and the following code and paste it in the Module window:
Sub RemovConditionalFormattingButKeepFormat()
For Each cell In Selection
With cell
.Font.FontStyle = .DisplayFormat.Font.FontStyle
.Font.Strikethrough = .DisplayFormat.Font.Strikethrough
.Interior.Pattern = .DisplayFormat.Interior.Pattern
If .Interior.Pattern <> xlNone Then
.Interior.PatternColorIndex = .DisplayFormat.Interior.PatternColorIndex
.Interior.Color = .DisplayFormat.Interior.Color
End If
.Interior.TintAndShade = .DisplayFormat.Interior.TintAndShade
.Interior.PatternTintAndShade = .DisplayFormat.Interior.PatternTintAndShade
End With
Next
Selection.FormatConditions.Delete
End Sub
  • Press F5 to run the code.

The conditional formatting is removed while keeping the formatting as it was. Now if we change the value of a cell in the Quantity column to greater than 50, its format won’t change.

Remove Conditional Formatting but Keep the Format


Notes

  • In the VBA code, we used the DisplayFormat property to keep the cell format as it is. We also applied the FormatConditons object to delete the conditional formatting.
  • To view the code, click the right button on the sheet name and select View Code.

Remove Conditional Formatting but Keep the Format


Download Practice Workbook


Related Articles

<< Go Back to Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

7 Comments
  1. Thanks, was trying to find this and it took way longer to find than it should have. Turns out all the Macros I didn’t work with latest version of Excel.

    One suggestion, this didn’t work for me until I added “Dim Cell as Range” as I have variable declaration on, so might want to add that line to eliminate that error mode.

    • Reply Avatar photo
      Osman Goni Ridwan Aug 30, 2022 at 11:31 AM

      Hello JUSTIN! The code is working perfectly from my side and the output has been shown in the practice workbook. And, it is not necessary to add code “Dim Cell as Range” here as we are not working with the cell value. But it is a good practice to declare variables at first.
      We have a large collection of Excel-related blogs that will help you to solve many more problems. Browse them and let us know your opinion in the comment section. Thank You!

  2. Thank you very much, Al Arafat Siddique!! You spared me a lot of time and headache trying to write myself the right code (I am not a professional programmer anf I am a bit of clumsy and slowly).

  3. this is 100x quicker…

    Sub RemoveConditionalFormatting()
    Dim cell As Range

    ‘ Loop through each cell in the selection
    For Each cell In Selection
    ‘ Clear only conditional formatting
    cell.FormatConditions.Delete
    Next cell
    End Sub

  4. How can I update this to also retain borders?

    • Dear CC,
      Thank you very much for reading our articles.
      You have requested an updated VBA code to retain borders as well. Please try the provided VBA code below; it preserves borders.

      
      Sub RemoveConditionalFormattingButKeepFormat()
          ' Loop through each cell in the selected range
          For Each cell In Selection
              ' With block to refer to the current cell
              With cell
                  ' Copy font color from the display format to the cell
                  .Font.Color = .DisplayFormat.Font.Color
      
                  ' Copy border settings from the display format to the cell
                  .Borders.LineStyle = .DisplayFormat.Borders.LineStyle
      
                  ' Copy interior settings from the display format to the cell
                  .Interior.Pattern = .DisplayFormat.Interior.Pattern
      
                  ' If the interior pattern is not xlNone (no pattern), copy additional interior settings
                  If .Interior.Pattern <> xlNone Then
                      .Interior.PatternColorIndex = .DisplayFormat.Interior.PatternColorIndex
                      .Interior.Color = .DisplayFormat.Interior.Color
                  End If
      
                  ' Copy TintAndShade settings from the display format to the cell
                  .Interior.TintAndShade = .DisplayFormat.Interior.TintAndShade
      
                  ' Copy PatternTintAndShade settings from the display format to the cell
                  .Interior.PatternTintAndShade = .DisplayFormat.Interior.PatternTintAndShade
              End With
          Next
      
          ' Delete all format conditions from the selected range
          Selection.FormatConditions.Delete
      End Sub
      

      Best Regards,
      Alok
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo