Delete Individual FormatCondition gives 1004 Error
 

Hi

I am trying to search through FormatConditions in my Worksheet that
intersect with a target range and delete them. When I attempt to delete a
single FormatCondition I get a 1004 error.

In addition I am looking for equivalent functionality to Excel 2007's
FormatCondition's AppliesTo Property in Excel 2003 if any one has any
pointers.

A sample macro that demonstrates the deletion problem is below: -

Dim s As Worksheet
Set s = Sheets("Sheet1")

Dim fc As FormatCondition

' clear the entire sheet first
s.Cells.FormatConditions.Delete

' set up 3 overlapping ranges
' 1:5, 3:7, 5:9 = i*2-1:i*2+3
Dim ranges(1 To 3) As Range
Dim i As Integer
For i = 1 To 3
Set ranges(i) = s.Range(s.Cells(i * 2 - 1, i * 2 - 1), s.Cells(i * 2
+ 3, i * 2 + 3))
Set fc = ranges(i).FormatConditions.Add(xlExpression, xlEqual,
"=MOD(ROW()+16,2)0")
fc.Interior.Color = RGB(0, 0, 70 + 60 * i)
Next i

' want to delete any FormatCondition intersecting
' with targetRange. loop through all FCs on sheet
' and delete if intersects.
Set targetRange = Sheets("Sheet1").Range("F3:F3")

For Each fc In s.Cells.FormatConditions
'MsgBox fc.AppliesTo.Address
If Not Intersect(fc.AppliesTo, targetRange) Is Nothing Then
' want to delete this FC but AppliesTo only works in XL2007
fc.AppliesTo.Select
End If
Next fc

' actually just trying to delete an individual
' formatcondition gives an 1004 error
s.Cells.FormatConditions(2).Delete

Has any one had any similar problems?
Thanks

Delete Individual FormatCondition gives 1004 Error
 

Make sure there is a format condition before you do the delete. try code
like this

For i = 1 To Range("B6").FormatConditions.Count
Range("B6").FormatConditions(i).Delete
Next i

"bsdz" wrote:

Hi

I am trying to search through FormatConditions in my Worksheet that
intersect with a target range and delete them. When I attempt to delete a
single FormatCondition I get a 1004 error.

In addition I am looking for equivalent functionality to Excel 2007's
FormatCondition's AppliesTo Property in Excel 2003 if any one has any
pointers.

A sample macro that demonstrates the deletion problem is below: -

Dim s As Worksheet
Set s = Sheets("Sheet1")

Dim fc As FormatCondition

' clear the entire sheet first
s.Cells.FormatConditions.Delete

' set up 3 overlapping ranges
' 1:5, 3:7, 5:9 = i*2-1:i*2+3
Dim ranges(1 To 3) As Range
Dim i As Integer
For i = 1 To 3
Set ranges(i) = s.Range(s.Cells(i * 2 - 1, i * 2 - 1), s.Cells(i * 2
+ 3, i * 2 + 3))
Set fc = ranges(i).FormatConditions.Add(xlExpression, xlEqual,
"=MOD(ROW()+16,2)0")
fc.Interior.Color = RGB(0, 0, 70 + 60 * i)
Next i

' want to delete any FormatCondition intersecting
' with targetRange. loop through all FCs on sheet
' and delete if intersects.
Set targetRange = Sheets("Sheet1").Range("F3:F3")

For Each fc In s.Cells.FormatConditions
'MsgBox fc.AppliesTo.Address
If Not Intersect(fc.AppliesTo, targetRange) Is Nothing Then
' want to delete this FC but AppliesTo only works in XL2007
fc.AppliesTo.Select
End If
Next fc

' actually just trying to delete an individual
' formatcondition gives an 1004 error
s.Cells.FormatConditions(2).Delete

Has any one had any similar problems?
Thanks

Delete Individual FormatCondition gives 1004 Error
 

I think it need to decrement instead of increment

For i = Range("B6").FormatConditions.Count to 1 step -1
Range("B6").FormatConditions(i).Delete
Next i

"bsdz" wrote:

Hi

I am trying to search through FormatConditions in my Worksheet that
intersect with a target range and delete them. When I attempt to delete a
single FormatCondition I get a 1004 error.

In addition I am looking for equivalent functionality to Excel 2007's
FormatCondition's AppliesTo Property in Excel 2003 if any one has any
pointers.

A sample macro that demonstrates the deletion problem is below: -

Dim s As Worksheet
Set s = Sheets("Sheet1")

Dim fc As FormatCondition

' clear the entire sheet first
s.Cells.FormatConditions.Delete

' set up 3 overlapping ranges
' 1:5, 3:7, 5:9 = i*2-1:i*2+3
Dim ranges(1 To 3) As Range
Dim i As Integer
For i = 1 To 3
Set ranges(i) = s.Range(s.Cells(i * 2 - 1, i * 2 - 1), s.Cells(i * 2
+ 3, i * 2 + 3))
Set fc = ranges(i).FormatConditions.Add(xlExpression, xlEqual,
"=MOD(ROW()+16,2)0")
fc.Interior.Color = RGB(0, 0, 70 + 60 * i)
Next i

' want to delete any FormatCondition intersecting
' with targetRange. loop through all FCs on sheet
' and delete if intersects.
Set targetRange = Sheets("Sheet1").Range("F3:F3")

For Each fc In s.Cells.FormatConditions
'MsgBox fc.AppliesTo.Address
If Not Intersect(fc.AppliesTo, targetRange) Is Nothing Then
' want to delete this FC but AppliesTo only works in XL2007
fc.AppliesTo.Select
End If
Next fc

' actually just trying to delete an individual
' formatcondition gives an 1004 error
s.Cells.FormatConditions(2).Delete

Has any one had any similar problems?
Thanks

Delete Individual FormatCondition gives 1004 Error
 

Tbh that is what I tried first. If you look at the loop above my individual
deletion, that is where I need to delete the FormatCondition and that is
where I tried first. I suspect this is a bug in Excel. I will probably file
it with MS.

Thanks in any case :)

"Joel" wrote:

I think it need to decrement instead of increment

For i = Range("B6").FormatConditions.Count to 1 step -1
Range("B6").FormatConditions(i).Delete
Next i

"bsdz" wrote:

Hi

I am trying to search through FormatConditions in my Worksheet that
intersect with a target range and delete them. When I attempt to delete a
single FormatCondition I get a 1004 error.

In addition I am looking for equivalent functionality to Excel 2007's
FormatCondition's AppliesTo Property in Excel 2003 if any one has any
pointers.

A sample macro that demonstrates the deletion problem is below: -

Dim s As Worksheet
Set s = Sheets("Sheet1")

Dim fc As FormatCondition

' clear the entire sheet first
s.Cells.FormatConditions.Delete

' set up 3 overlapping ranges
' 1:5, 3:7, 5:9 = i*2-1:i*2+3
Dim ranges(1 To 3) As Range
Dim i As Integer
For i = 1 To 3
Set ranges(i) = s.Range(s.Cells(i * 2 - 1, i * 2 - 1), s.Cells(i * 2
+ 3, i * 2 + 3))
Set fc = ranges(i).FormatConditions.Add(xlExpression, xlEqual,
"=MOD(ROW()+16,2)0")
fc.Interior.Color = RGB(0, 0, 70 + 60 * i)
Next i

' want to delete any FormatCondition intersecting
' with targetRange. loop through all FCs on sheet
' and delete if intersects.
Set targetRange = Sheets("Sheet1").Range("F3:F3")

For Each fc In s.Cells.FormatConditions
'MsgBox fc.AppliesTo.Address
If Not Intersect(fc.AppliesTo, targetRange) Is Nothing Then
' want to delete this FC but AppliesTo only works in XL2007
fc.AppliesTo.Select
End If
Next fc

' actually just trying to delete an individual
' formatcondition gives an 1004 error
s.Cells.FormatConditions(2).Delete

Has any one had any similar problems?
Thanks