Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
What code could I use to delete all Conditional Formatting in the
active workbook? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
On Wed, 14 Jul 2010 06:23:39 -0700 (PDT), Seanie
wrote: What code could I use to delete all Conditional Formatting in the active workbook? Thanks What version of Excel? In Excel 2007, this code should work. I'm not sure about earlier codes. I set a worksheet name because, for testing, it happens to be a sheet that has a number of conditional format cells, but you probably want to change that to activesheet or a specific sheet(s) depending on your specific requirements: ================================ Option Explicit Sub DeleteConditionalFormats() Dim ws As Worksheet Dim r As Range, c As Range Set ws = Worksheets("Solver") On Error GoTo NoCellsFound ws.Cells.SpecialCells(xlCellTypeAllFormatCondition s).FormatConditions.Delete Exit Sub NoCellsFound: MsgBox ("No Cells Found") End Sub ================================= |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
Thanks Ron, 2007 is the version
How can I tweak to delete on all sheets in the active workbook? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
Iterate through the sheets.
Option Explicit Sub DeleteConditionalFormats() Dim ws As Worksheet Dim r As Range, c As Range On Error GoTo NoCellsFound For Each ws In ActiveWorkbook.Worksheets ws.Cells.SpecialCells(xlCellTypeAllFormatCondition s).FormatConditions.Delete Next ws Exit Sub NoCellsFound: MsgBox ("No Cells Found") End Sub Note: if any sheet has no CF you will get the error. Gord Dibben MS Excel MVP On Wed, 14 Jul 2010 07:25:52 -0700 (PDT), Seanie wrote: Thanks Ron, 2007 is the version How can I tweak to delete on all sheets in the active workbook? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
On Wed, 14 Jul 2010 07:25:52 -0700 (PDT), Seanie
wrote: Thanks Ron, 2007 is the version How can I tweak to delete on all sheets in the active workbook? You iterate through all the sheets, and rework the error handler to keep track of sheets with no CF's. You could also display more information if you want: =================================== Option Explicit Sub DeleteConditionalFormats() Dim ws As Worksheet Dim r As Range, c As Range Dim sNoCF() As String Dim i As Long, s As String ReDim sNoCF(0) For Each ws In ActiveWorkbook.Worksheets On Error GoTo NoCellsFound ws.Cells.SpecialCells(xlCellTypeAllFormatCondition s).FormatConditions.Delete Next ws s = Join(sNoCF, vbLf) MsgBox ("The following sheets did not have any cells with CF:" & s) Exit Sub NoCellsFound: On Error GoTo 0 ReDim Preserve sNoCF(UBound(sNoCF) + 1) sNoCF(UBound(sNoCF)) = ws.Name Resume Next End Sub ================================== |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
What am I doing wrong on below which debugs with message "no cells
found", I tried to tweak to delete all CF in all sheets, without any message box display Sub DeleteConditionalFormats() Dim ws As Worksheet Dim r As Range, c As Range On Error GoTo 0 For Each ws In ActiveWorkbook.Worksheets ws.Cells.SpecialCells(xlCellTypeAllFormatCondition s).FormatConditions.Delete Next ws Exit Sub End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
On Sat, 17 Jul 2010 04:52:15 -0700 (PDT), Seanie
wrote: What am I doing wrong on below which debugs with message "no cells found", I tried to tweak to delete all CF in all sheets, without any message box display You have no code to handle the error that will occur when there are no conditionally formatted cells on a worksheet. Sub DeleteConditionalFormats() Dim ws As Worksheet Dim r As Range, c As Range On Error GoTo 0 For Each ws In ActiveWorkbook.Worksheets ws.Cells.SpecialCells(xlCellTypeAllFormatConditio ns).FormatConditions.Delete Next ws Exit Sub End Sub Perhaps: ======================= Option Explicit Sub DeleteConditionalFormats() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets On Error Resume Next ws.Cells.SpecialCells(xlCellTypeAllFormatCondition s).FormatConditions.Delete Next ws End Sub ========================== |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
Thanks Ron, I thought "On Error GoTo 0" handled that
One further twist, delete all CF except in Sheets1; Sheet2; Sheet3? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Conditional Formatting Q
On Sat, 17 Jul 2010 09:34:48 -0700 (PDT), Seanie
wrote: Thanks Ron, I thought "On Error GoTo 0" handled that You need to read VBA HELP for the On Error statement There is an error in my code in that it does not reset the error function. It should read: ================= Option Explicit Sub DeleteConditionalFormats() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets On Error Resume Next ws.Cells.SpecialCells(xlCellTypeAllFormatCondition s).FormatConditions.Delete On Error GoTo 0 Next ws End Sub =================== One further twist, delete all CF except in Sheets1; Sheet2; Sheet3? Just test to see which worksheet you are cycling on; then skip the loop if it is a sheet you're not interested in. =========================================== Option Explicit Sub DeleteConditionalFormats() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Sheet1" And _ ws.Name < "Sheet2" And _ ws.Name < "Sheet3" Then On Error Resume Next ws.Cells.SpecialCells(xlCellTypeAllFormatCondition s).FormatConditions.Delete On Error GoTo 0 End If Next ws End Sub ====================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - How do I delete ALL rules? | Excel Discussion (Misc queries) | |||
delete conditional formatting via macro | Excel Discussion (Misc queries) | |||
How can I use conditional formatting to hide or delete rows? | Excel Worksheet Functions | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Macro code to delete conditional formatting | Excel Discussion (Misc queries) |