Setting up Conditional Formatting in VBA

[Guest]

I'm trying to set up conditional formatting for a range in my VBA code, but
I'm not having any luck. Is conditional format setup only available from the
user interface menu? I see that there's a ConditionalFormats collection as
well as a ConditionalFormat object that I can create and add to a collection,
but how do I define the formula for the conditional format? If I do it on the
UI menu, I would use "Formula Is" and some formula. However, I'd like to do
this in my VBA code. Any ideas? Thanks in advance.

 

[JK]

Bruce,

You can try this out if you like.

To get rid of any conditional formating on your selected cell use:
Selection.FormatConditions.Delete

To add a conditional format, use something like this:
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=fOperator,
Formula1:=fFormula1, Formula2:=fFormula2

To get to a certain condition (I think there can be up to 3) and
change the settings use something like:
Selection.FormatConditions(1).Font.Bold = False
or
Selection.FormatConditions(1).Interior.ColorIndex = 15

Of course you can replace "Selection" with a Range or Cell if you like.


Hope that helps,

-JK