The DisplayFormat OM

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.