Excel

Delete Conditional Formatting

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Zack Barresse

Description:

All selected cells will have the Conditional Formatting removed. 

Discussion:

Conditional Formatting can be a very nice tool. In Excel's current versions, we can use up to 3 conditional formats to alter the appearance of cells (font, bold, italics, font color, background color, etc). It may be advantageous and time saving to delete these at the click of a button rather than going in and deleting them by hand for multiple cells. This code will delete all conditional formats within the users selected area with a confirmation box first. 

Code:

instructions for use 

			
Option Explicit 
 
Public Sub DeleteCF() 
     
     'Check to see if a workbook is open or not.
     'If not workbook is open a message box will
     ' tell you so and quit routine.
    If ActiveWorkbook Is Nothing Then 
        MsgBox "You must have a workbook open first!", vbInformation, "No Open Book" 
        Exit Sub 
    End If 
     
     'Message box asks user if they are sure.
    If MsgBox("Delete all Conditional Formats from selection?", _ 
    vbYesNo, "Remove Conditions") = vbNo Then Exit Sub 
     
     'Perform action (delete all conditional formats from selected cells).
    Selection.FormatConditions.Delete 
     
End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE (Visual Basic Editor).
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Select Insert -> Module.
  6. Paste code into the right (blank) pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.
 

Test the code:

  1. Select all cells with conditional formats that you would like removed (can be non-contiguous).
  2. Press Alt + F8.
  3. Select 'DeleteCF'.
  4. Press Run.
 

Sample File:

DeleteCF.zip 7.35KB 

Approved by mdmackillop


This entry has been viewed 148 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express