How to Remove Conditional Formatting in Excel (3 Examples)

In Excel, Conditional Formatting is a handy feature that allows you to effortlessly format and shade cells based on certain conditional criteria. However, you may wish to delete or clear conditional formatting in Excel at times. In this tutorial, we’ll explain to you how to remove Conditional Formatting in different ways including the Clear Rules function and the application of VBA.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Examples to Remove Conditional Formatting in Excel

A sample data set of average yearly salaries at Google’s multiple departments are shown in the screenshot below. We’ve highlighted the incomes with an average salary of more than $120,000 using Conditional Formatting. However, you may need to remove conditional formatting after applying it.

We’ll demonstrate to you three different ways to remove conditional formatting. To begin, we’ll use the usual method. Then, using VBA code, we’ll eliminate conditional formatting whilst also leaving the format intact.

Apply Basic Method to Remove Conditional Formatting

1. Apply Basic Method to Remove Conditional Formatting

In the beginning, we will apply the Clear Rules command to remove Conditional Formatting. It’s usually the basic method to remove Conditional Formatting. To apply these methods, simply follow the steps.

Step 1:

  • Firstly, select the range where you applied Conditional Formatting.

Apply Basic Method to Remove Conditional Formatting

Step 2:

  • Click on Home.

Apply Basic Method to Remove Conditional Formatting

Step 3:

  • Firstly, click on the Conditional Formatting
  • Select the Clear Rules
  • Finally, select Clear Rules from Selected Cells from the menu.

Apply Basic Method to Remove Conditional Formatting

  • Therefore, you’ll see that your Conditional Formatting is no longer present.

Apply Basic Method to Remove Conditional Formatting


2. Run a VBA Code to Remove Conditional Formatting

In this section, we’ll explain to you how to use VBA code to remove Conditional Formatting as well. It’s a unique approach, but it’s one that works. Because you can simply select the range and remove the Conditional Formatting to apply the same code countless times. To run a VBA code, follow the steps below.

Step 1:

  • Firstly, press Alt + F11 to activate VBA Macro-Enabled Worksheet.
  • Choose Insert from the tab.
  • Then, Select Module from the options.

Run a VBA Code to Remove Conditional Formatting

Step 2:

  • After selecting a Module, just paste the following VBA.
Sub DeleteConditionalFormats()
Dim WorkRng As Range
On Error Resume Next
xTitleId = "ExcelDemy"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
WorkRng.FormatConditions.Delete
End Sub

Here,

  • Dim WorkRng As Range is declaring WorkRng variable as a range value.
  • xTitleId = “ExcelDemy” is the title name that appeared in the input box.
  • Set WorkRng = Application.Selection refers to the range that will be from the current selection.
  • InputBox(“Range”, xTitleId, WorkRng.Address, Type:=8) refers to the input box that appears to get the range and is named with the title ‘ExcelDemy’.

WorkRng.FormatConditions.Delete refers to deleting all the Conditional Format between the range.

Run a VBA Code to Remove Conditional Formatting

Step 3:

  • Save the program and press F5 to run it.
  • A range box ‘ExelDemy’ will appear, select the range.
  • Finally, press Enter to see the changes.

Run a VBA Code to Remove Conditional Formatting

Consequently, Conditional Formatting will be removed from the cells as shown in the below screenshot.

Run a VBA Code to Remove Conditional Formatting


3. Run a VBA Code to Remove Conditional Formatting but Keep Format

In addition to the previous method, you may easily remove the Conditional Formatting yet preserve the format by using VBA code. In general, Excel functions do not permit this. Only the VBA code will allow you to accomplish this. That is VBA code’s supremacy over Excel Functions. To achieve this, follow the steps below.

Step 1:

  • To open the VBA Macro, press Alt + F11.
  • From the tabs, choose the Insert
  • Then, select Module.

Run a VBA Code

Step 2:

  • Paste the following VBA code.
Sub Remove_Condition_but_Keep_Format()
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
Set xRg = Application.InputBox("Select range:", "ExcelDemy", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
For Each xCell In xRg
With xCell
.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
xRg.FormatConditions.Delete
End Sub

Here,

  • xRg As Range refers to declaring xRg as a range.
  • xTxt As String refers to declaring xTxt as a string.
  • xCell As Range refers to xCell as a range.
  • On Error Resume Next refers to your code will continue to run even if an error occurs.
  • RangeSelection.Count refers to the selection of cells in the worksheet.
  • UsedRange.AddressLocal refers to the used range in the specified worksheet.
  • InputBox(“Select range:”, “ExcelDemy”, xTxt, , , , , 8) is the input box where you will input range when appeared with the title ‘ExcelDemy’.
  • .Font.FontStyle = .DisplayFormat.Font.FontStyle commands that font will remain as conditional formatting.
  • .Interior.PatternColorIndex = .DisplayFormat.Interior.PatternColorIndex command that will remain cell color as conditional formatting.
  • .Interior.TintAndShade = .DisplayFormat.Interior.TintAndShade commands that interior shade and object will remain as conditional formatting.

xRg.FormatConditions.Delete refers to delete all the conditional formatting for range for the string value under the range.

Run a VBA Code

Step 3:

  • A dialog box will appear, select the range.
  • Finally, click OK to see the results.

Run a VBA Code

Therefore, Conditional Formatting is deleted in the image below, but the cell format stays the same.

Run a VBA Code


Conclusion

To sum up, I hope this tutorial has shown you how to remove conditional formatting in Excel using both the simple method and VBA code. All of these techniques should be taught and used to your data. Examine the practice book and apply what you’ve learned. Because of your contribution, we are able to support projects like this.

Please do not hesitate to contact us if you have any queries. Please leave a comment below to let me know what you think.

Exceldemy staff will respond to your inquiries as quickly as possible.

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo