Adding FormatConditions in between other conditions
 

I'm using Excel 2003, and trying to write a method that will insert a format
condition at the passed in index. I know that to do this I essentially need
to delete the old conditions and re-add. Regardless I have the below, but it
seems the formatconditions objects are not new object and keep their link the
the original formatting, so if that formatting is deleted, so it the
variables data. Does anyone know if this can be done? Below is what I was
working on so far.

Public Sub InsertConditionalFormat(ByVal index As Integer, rCell As Range, _
formatCondition As formatCondition)

Dim cfShift As formatCondition
Dim colFC As New Collection

'First get format conditions of old cell
For Each cfShift In rCell.FormatConditions

colFC.Add cfShift

Next cfShift

rCell.FormatConditions.Delete

'Now insert new format condition where they want it
If index <= colFC.Count And colFC.Count < 3 Then

colFC.Add formatCondition, , index

End If

'Add format conditions back to the cell
For index = 1 To colFC.Count

Set cfShift = colFC(index)

rCell.FormatConditions.Add cfShift.Type, Formula1:=cfShift.Formula1

'rCell.FormatConditions(index) = cfShift

Next index

End Sub

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

User of MS Office 2003