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(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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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(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

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(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