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

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
>

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
>

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