Conditional Formatting in VBA in 2007

[peedub]

I have a series of non-contiguous rows that I want to apply
conditional formatting to. Right now, my code snip looks like this:

With Range(sBegRange, sEndRange)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sCFCell & ">40"
.FormatConditions(i).Font.Color = 3
.FormatConditions(i).StopIfTrue = False
End With

Where sCFCell is a string value for the cell I want evaluated for the
formula, and i is an integer that increments for each time I create a
new rule (which I'm doing for each row I format).

It's currently breaking on the .FormatConditions(i).Font.Color = 3
line, with an error reading "Application-defined or object-defined
error", error 1004. I'm guessing it doesn't like the "FormatConditions
(i)" part, so how do I get around this? How can I dynamically identify
a set of rows to build a single rule for? Is it possible to build the
rule the first time, then keep appending the new range of cells for
the rule to apply to?

 

[Bob Phillips]

With Range(sBegRange, sEndRange)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sCFCell & ">40"
.FormatConditions(i).Font.ColorIndex = 3
.FormatConditions(i).StopIfTrue = False
End With

or

With Range(sBegRange, sEndRange)
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & sCFCell & ">40"
.FormatConditions(i).Font.Color = vbRed
.FormatConditions(i).StopIfTrue = False
End With

 

[peedub]

No joy, Bob. Both approaches generated the same "Application-defined
or object-defined error" message as before. Thanks for the suggestion!

 

[peedub]

OK - I take it back. I went back and determined that my sCFCell
variable was pointing to a cell that generated an invalid test for >
40. SO now that I solved that riddle, here's what happens:

I am seeding the i variable value with 3, because prior to this block
of code I've already created two previous rules. The new rule (#3)
gets created just fine, but then the formatting definition for the
rule gets applied to the FIRST rule I created. When I go into the
conditional formatting UI, I see all three rules, but the format
definition for the first rule has been changed to what I had defined
for 3, and the third rule says "NO FORMAT DEFINED". I've tried setting
the i value to some other number, but it never results in the format
being defined to #3, it always applies to formatting to either #1 or
#2.

FWIW - the range of cells being formatted by the third rule is a
subset of the rows that were formatted by rules 1 and 2. Help for the
FormatConditions method indicates the (i) should be the index that
refers to my rule - which should be the number I indicated, right?