Conditional Formatting in VBA in 2007

  • Thread starter Thread starter peedub
  • Start date Start date
P

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?
 
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
 
No joy, Bob. Both approaches generated the same "Application-defined
or object-defined error" message as before. Thanks for the suggestion!
 
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?
 
Back
Top