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.
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.
- Firstly, select the range where you applied Conditional Formatting.
- Click on Home.
- Firstly, click on the Conditional Formatting
- Select the Clear Rules
- Finally, select Clear Rules from Selected Cells from the menu.
- Therefore, you’ll see that your Conditional Formatting is no longer present.
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.
- Firstly, press Alt + F11 to activate VBA Macro-Enabled Worksheet.
- Choose Insert from the tab.
- Then, Select Module from the options.
- 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
- 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.
- 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.
Consequently, Conditional Formatting will be removed from the cells as shown in the below screenshot.
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.
- To open the VBA Macro, press Alt + F11.
- From the tabs, choose the Insert
- Then, select Module.
- 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
- 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.
- A dialog box will appear, select the range.
- Finally, click OK to see the results.
Therefore, Conditional Formatting is deleted in the image below, but the cell format stays the same.
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.