Delete single conditional format

[Ron Rosenfeld]

Excel 2007 / VBA 6.5

Is it possible to delete a single conditional format? (My goal is to delete
all except 2.

I have tried to do so (code below) and receive a 1004 error.

When recording a macro while deleting two of the formats, it appears to delete
ALL of the formats, and then recreate the formats that remain.

Here is the code I tried:

=============================
Private Sub Worksheet_Activate()
Dim i As Long

With Worksheets("solver").Cells
If .FormatConditions.Count > 2 Then
For i = 1 To .FormatConditions.Count - 2
.FormatConditions(i).Delete
Next i
End If
End With
End Sub
===========================
--ron

 

[Peter T]

It might depend on the particular CFs as to whether you can delete them
individually. However, you won't do it with the code as posted, have a go
with something like this

Sub test()
Dim i As Long
Dim c As Range, r As Range

On Error Resume Next
Set r =
Worksheets("sheet1").Cells.SpecialCells(xlCellTypeAllFormatConditions)
On Error GoTo errExit

If Not r Is Nothing Then
For Each c In r
With c.FormatConditions
If .Count > 2 Then
For i = .Count To 3 Step -1
.Item(i).Delete
Next
End If
End With
Next
End If
Exit Sub

errExit:
' for debugging only
c.Select
Debug.Print c.Address
Stop
Resume Next

End Sub

Regards,
Peter T

 

[Ron Rosenfeld]

It might depend on the particular CFs as to whether you can delete them
individually. However, you won't do it with the code as posted, have a go
with something like this

Sub test()
Dim i As Long
Dim c As Range, r As Range

On Error Resume Next
Set r =
Worksheets("sheet1").Cells.SpecialCells(xlCellTypeAllFormatConditions)
On Error GoTo errExit

If Not r Is Nothing Then
For Each c In r
With c.FormatConditions
If .Count > 2 Then
For i = .Count To 3 Step -1
.Item(i).Delete
Next
End If
End With
Next
End If
Exit Sub

errExit:
' for debugging only
c.Select
Debug.Print c.Address
Stop
Resume Next

End Sub

Regards,
Peter T

Thanks, Peter.

I'll give it a try.
--ron

 

[Ron Rosenfeld]

It might depend on the particular CFs as to whether you can delete them
individually. However, you won't do it with the code as posted, have a go
with something like this

Sub test()
Dim i As Long
Dim c As Range, r As Range

On Error Resume Next
Set r =
Worksheets("sheet1").Cells.SpecialCells(xlCellTypeAllFormatConditions)
On Error GoTo errExit

If Not r Is Nothing Then
For Each c In r
With c.FormatConditions
If .Count > 2 Then
For i = .Count To 3 Step -1
.Item(i).Delete
Next
End If
End With
Next
End If
Exit Sub

errExit:
' for debugging only
c.Select
Debug.Print c.Address
Stop
Resume Next

End Sub

Regards,
Peter T

Well, a variation of what you posted is working perfectly OK. I'm guessing a
pertinent point to what I missed is that I can't delete individual CF's unless
I loop through each cell (although I can delete them all).

Thanks for your help.
--ron