Delete single conditional format
 

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

Delete single conditional format
 

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

Delete single conditional format
 

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

Delete single conditional format
 

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