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

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(xlCellType AllFormatConditions)
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" wrote in message
...
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

On Sat, 11 Jul 2009 13:42:12 +0100, "Peter T" <peter_t@discussions wrote:

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(xlCellTyp eAllFormatConditions)
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

On Sat, 11 Jul 2009 13:42:12 +0100, "Peter T" <peter_t@discussions wrote:

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(xlCellTyp eAllFormatConditions)
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