Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
Calculate and display individual error bars for individual points | Charts and Charting in Excel | |||
Simple For-Loop gives 1004 error using variable to EntireRow.Delete | Excel Programming | |||
runtime error '1004' delete Method of Range Class Failed | Excel Programming | |||
runtime error '1004' delete Method of Range Class Failed | Excel Programming |