You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Since I have had some comments and emails asking about how the new conditional formatting features could be accessed using VBA, I wanted to provide a few brief examples. One of the Excel team’s principles is that when we add new features, we make sure that they are available programmatically as well as in the user interface. The Excel 12 object model, accordingly, supports all conditional formatting functionality that is supported in the UI. This includes creating, editing, or deleting rules, or changing priorities on rules.
As folks who have written conditional formatting VBA in previous versions of Excel will know, the FormatConditions collection hangs off the Range object. Let me briefly run through some examples of how our new functionality is exposed in the FormatConditions collection.
Creating a rule:
The new conditional formatting rules we have introduced in Excel 12 (Databars, Color Scales, Icon Sets, Top n, etc.) can be created using the Add<objectname> method in the FormatConditions collection. For example, to add a Databar, run:
Editing the rule:
To edit the rule, index into the FormatConditions collection and modify the properties. For example, to change the bar color, run:
Range("A1:A5").FormatConditions(1).BarColor.ColorIndex = 3
Here, the number 1 indexes the first rule on the range.
Editing the priority:
In Excel 12, we introduced the idea of rule priorities to support multiple conditions on a range. The priority determines the order of application of the rule. In the object model, we also have the Priority property on the FormatConditions object. This property is tracked at a sheet level. For example, to verify the priority of a rule, run:
To make this rule the lowest priority:
To assign a specific priority:
Range("A1:A5").FormatConditions(1).Priority = 3
Note that if you had three rules, setting the priortity to be 3 and using SetLastPriority would have the same effect.
Deleting the rule:
You can delete a specific rule by indexing into it and then calling the Delete method
To delete all rules in the specific range, call the Delete method on the FormatConditions collection.
Here’s another example. Imagine you wanted to write VBA to highlight the Top 5% of the values in the range A1-A10 with a red fill. Here is the code snippet for this:
'Adding the Top10 rule to the range
'Assign the rank of the condition to 5
Range("A1:A10").FormatConditions(1).Rank = 5
‘Set the Percent property true. It is false by default.
Range("A1:A10").FormatConditions(1).Percent = True
'Set the color to a red fill
Range("A1:A10").FormatConditions(1).Interior.ColorIndex = 3
Hopefully these examples are useful.
One other thing before I sign off - Jensen Harris just posted a topic on keyboard access and the ribbon, which is something that is important to Excel users, so you might want to take a look.
One common request has been if we'll be able to see which conditional format is active. Is this possible in O12 ?
Juan Pablo González...
|One common request has been if we'll be able
|to see which conditional format is active. Is
|this possible in O12 ?
We can already iterate through conditions, though it's a pain to evaluate formula conditions (due to needing to evaluate in the context of particular cells). But if in XL12 multiple conditions could be true, we'd either need to store arrays or collections of satisfied criteria or process them in order of priority.
Life would be easier if Range objects had an .Evaluate method rather than having to activate each cell or modify formula criteria.
From what I've seen, most situations where people are trying to evaluate CFs is actually to work out the *applied* formatting of the range. So Range.Interior.Color will give the un-CFd colour, but it would be great to have something to tell us what the applied formatting is, perhaps as properties of the FormatConditions object?
How about the XLM (macro-) support. Is it in-or-out in this version of Excel. I'am curious ...
will this work in excel 2002?
i'm getting an error:
runtime error '438'
object doesn't support this property or method
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.
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.
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).
This would address my needs I think:
Dim ActiveCondition as FormatCondition
Set ActiveCondition= Rg.FormatConditions.ConditionInEffect
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.
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)?
David - Definetely yes to include formatting from styles. Thank you.
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.