terra incognita A broad view of Microsoft Office

The DisplayFormat OM

This post is syndicated from Microsoft Excel.

New features in conditional formatting extend to the object model as well. Excel has traditionally allowed users to access the formatting of their cells through the object model. For example, Users can use the interior class which lives off the range object to access the formatting of the cell. Selection.Interior.Color returns the background color of the cell.

However, Selection.Interior.Color does not take into account the conditional formatting applied to the cell.  In Excel 2010, we’ve introduced a new class called DisplayFormat which lives off the range object.  This class provides an easy way to access the formatting applied by conditional formatting rules and other things like table styles that format the cells but don't really reflect it in the cell formatting properties today.

For example, the following code gets the fill color of the active cell (after taking into account any conditional formatting applied to the cell):

Selection.DisplayFormat.Interior.Color

Let us consider a simple example as to how the DisplayFormat object might be used.  A bookstore provides bonuses to its employees based on how well they have met their sales targets for the year. The bonuses are awarded as follows:

• 15% bonus – Sales targets have been met for each quarter for the year.
• 5% bonus – Sales target was met for at least one quarter during the year.
• No Bonus – Failed to meet even a single sales target.

At the start of each year, the bookstore compiles a sales target for each employee as follows:

Then, the bookstore records the actual sales on a separate worksheet and uses conditional formatting to highlight quarters where the employees failed to meet their sales target.  These quarters are highlighted with a red background as follows:

Since the bonuses awarded at the end of the year depend on how well the salespeople met their targets, you can imagine that a formula to compute the bonus percentage would be quite complex.  Using the new DisplayFormat object, you can easily write custom user defined functions that leverage the smartness in conditional formatting rules to compute the bonuses.

In this case, the function to compute the employee bonuses looks as follows:

Function BonusAward(quarters As Range) As Double

Dim iBelowTarget As Integer ' Number of cells below target

' initialize variables

BonusAward = 0.15 'default 15% bonus-met target for all qtrs

iBelowTarget = 0

' loop through each cell

For Each qCell In quarters

'if conditional formatting cell background is NOT red(255)

If qCell.DisplayFormat.interior.color > 255 Then

  BonusAward = 0.05 ' award less bonus (failed quarter)

  iBelowTarget = iBelowTarget + 1

End If

Next qCell

' Award zero bonus if all quarters were below target

If iBelowTarget = quarters.Cells.count Then BonusAward = 0

End Function

The DisplayFormat object will provide access to conditional formatting results using many existing classes and properties such as the Borders, Characters, Font, Interior and Style.  Since we use existing classes and properties, it means that you can easily extend your existing code to work with it.

This wraps up my series of posts on conditional formatting. In our next Excel 2010 post we’ll continue on the theme of data visualization improvements by discussing some things we did with charts.

More Conditional Formatting Features in Excel 2010

This post is syndicated from Microsoft Excel.

In this blog post I’ll walk through some of the new Conditional Formatting features in Excel 2010 such as:

• Cross-Sheet References
• Robust Error Handling
• Performance Enhancements

Cross-Sheet References

In Excel 2010, you can create conditional formatting with references to a different sheet on the workbook. This means that you no longer need to copy or link your data to the same sheet when using conditional formatting.

For Example, let us look at the monthly sales data for a bookstore chain. The bookstore organizes their sales data as follows:

• Each location gets its own worksheet
• The last worksheet contains the cumulative company wide sales figures with the averages for each department.

We will apply conditional formatting so that we can easily determine if the department at the local branch is doing better or worse as compared to the other branches in the company. Let us use the San Francisco branch as our example. We will apply a green background fill for the departments if they are performing better than average.

1.  Start on the sheet for San Francisco location and select the cells in the sales column to which you want to apply conditional formatting. Go to the Home Tab-> Conditional formatting -> Highlight Cell Rules -> Greater Than.

2.  Click on the (RefEdit icon) and simply navigate to the sheet whose value you want to compare against. In our case, we will navigate to the company wide worksheet and click on the average for the first row in the department column.

3.  Now here is a neat little tip – references in Conditional Formatting follow all the rules of absolute and relative references. The Cell References are relative to the active cell in the “Applies To” range (in this case the top-left cell in the range).

Let us modify the reference to be relative to the rows by deleting “$” symbol before the row number.

The result is that each department on the “San Francisco” sheet being compared to the same type of department on the “Company” sheet.

4.  Click OK and you have a Conditional Formatting Rule that is dependent on other sheets in the workbook.

Error Handling

Have you ever used ranged based conditional formatting (such as icon sets, color scales and data bars) only to find that one of your formulas have evaluated to an error?  In Excel 2007, errors in the range would lead to conditional formatting not being applied to all the cells in the range.  Users told us that this was frustrating.

In Excel 2010, conditional formatting silently ignores the errors and continues applying conditional formatting to the rest of the cells in the range.

Performance Enhancements

For this release we’ve also improved the performance of formatting in Excel 2010. For conditional formatting we store dependencies of the formulas used so we don’t need to reevaluate the entire conditional format as often. We’ve also made banding in the table and pivot table styles faster, so this will impact things like refreshing a pivot table or scrolling with a large table on your sheet. For all the above cases, we also cache the format of the cell so if you scroll off the screen and come back, or switch sheets and come back, we’ll remember the cell formatting and can display faster.

In our next Excel 2010 post, we’ll look at a new object model feature for conditional formatting.