Conditional formatting using VBA - some examples

re: Conditional formatting using VBA - some examples

Stephen Bullen — Wed, 19 Oct 2005 17:59:04 GMT

David - All the scenarios I can think of are based around doing things according to what (formatting) the user is looking at - wherever that visual display came from or is affected by.

re: Conditional formatting using VBA - some examples

Jean-Marc Decouleur — Wed, 19 Oct 2005 10:29:37 GMT

David - Definetely yes to include formatting from styles. Thank you.

re: Conditional formatting using VBA - some examples

David Gainer — Wed, 19 Oct 2005 04:45:50 GMT

Greetings everyone. Thanks for the clarification and additional feedback. Sounds like there are two scenarios - one that requires simply knowing whether a condition is true or false, and a second that describes the end formatting. Curious - would that include formatting from styles (will be much more prominent this release) and server formatting (in the case the cell is getting its data from something like Analysis Server)?

re: Conditional formatting using VBA - some examples

Stephen Bullen — Tue, 18 Oct 2005 17:21:25 GMT

Jim - Yes, assuming that ConditionInEffect is a composite of all 'on' conditions (as we can have multiple on at one time). So if one condition set the font bold, another put a blue background and a third made the text yellow, the ConditionInEffect would return a FormatCondition object that had Font=Yellow Bold, Interior.Color=Blue. That'd work really well if it started off with the range's default formatting and overwrote the bits changed by each condition.

re: Conditional formatting using VBA - some examples

Jim Rech — Tue, 18 Oct 2005 14:30:47 GMT

David, Stephen-

This would address my needs I think:

Dim ActiveCondition as FormatCondition

Set ActiveCondition= Rg.FormatConditions.ConditionInEffect

re: Conditional formatting using VBA - some examples

Stephen Bullen — Tue, 18 Oct 2005 11:34:23 GMT

David - There are quite a lot of situations where we want to be able to determine using VBA what is actually presented to the user, such as a UDF to perform calculations based on colour, font style etc (e.g. "Sum of Red cells"). In Excel 2003, we have to iterate through the conditions, evaluate them to see if they're active (which is not easy if they're using relative formulae) and then see if they set the background colour to Red. In Excel 12, we'd also have to see if there are multiple conditions that evaluate to True, try to remember which has precedence (first or last?) etc. It's extremely frustrating to see that Excel must have already done the evaluation to know what to display, but that isn't presented in the object model. Hence the need/request for a Range.VisibleFormat property, which will return the formatting that the user is actually seeing (regardless of whether that came from the range's base formatting or conditional formatting).

What about charts?

Helen — Tue, 18 Oct 2005 11:28:06 GMT

Agree, a FormatCondition.Evaluate method (which would return True or False) would be very handy.

In the past I've needed to read the conditional formatting of a range (evaluate the conditions, find the active condition, get its format) in ordre to "conditionally" format a chart or a shape. Sometimes it's possible to do the chart formatting by using separate series for the different conditions, but not always. If Excel12 supported conditional formatting of charts, then most of those cases would no longer need a VBA solution.

Shapes will always require a VBA solution, of course.

re: Conditional formatting using VBA - some examples

David Gainer — Tue, 18 Oct 2005 01:25:21 GMT

Greetings

Juan Pablo, Harlan – thanks for the feedback. Currently, you would have to do this by iterating through the conditions as Harlan suggested. What sort of scenarios require you to check for the active conditional format?

Stephen - Correct, Range.Interior.Color does not give the conditional formatting color. Instead, you can use the Interior.Color property on the FormatConditions object like: ?ActiveCell.FormatConditions(1).Interior.Color … this will get you the color of the cell based on the conditional formatting rule.

Lenie - Is this a question about XLM support in general? If so, yes, XLM will be supported in Excel 12.

Mike – The specific examples I gave are all Excel 12 only, as earlier versions of Excel don’t support this data bars, top 10, or re-ordering conditions. The FormatConditions collection does exist in Excel 97 – Excel 2003, so you can programmatically work with the conditional formatting functionality available in those versions.

re: Conditional formatting using VBA - some examples

Mike — Mon, 17 Oct 2005 17:18:34 GMT

will this work in excel 2002?

i'm getting an error:

runtime error '438'

object doesn't support this property or method

thanks

Mike

re: Conditional formatting using VBA - some examples

Lenie Hobo — Mon, 17 Oct 2005 11:10:32 GMT

How about the XLM (macro-) support. Is it in-or-out in this version of Excel. I'am curious ...