Share via

OfficeTalk: Conditional Formatting: Adding Custom Conditional Formatting to Excel 2007

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary: Learn about enhancements to conditional formatting and how you can create custom conditional formatting options by using the Microsoft Office Fluent Ribbon in Microsoft Office Excel 2007. (6 printed pages)

Frank Rice, Microsoft Corporation

January 2007

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007

Contents

  • Overview

  • Enhancements to Office Excel 2007

  • Creating the Project

  • Conclusion

  • More About Conditional Formatting in Excel 2007

Overview

Conditional formatting is a popular feature documented in many books and articles. One of the exciting enhancements in Microsoft Office Excel 2007 is improvements to conditional formatting. By using conditional formatting in Office Excel 2007, you can apply formatting automatically to one or more cells based on the value of the cell or the value of a formula. This feature makes it simple to highlight specific values or to identify values in a range. For example, conditional formatting can answer questions such as "Show me the top 10 percent of all sales for North America" or "Show me just Nancy's sales for the year." It's a powerful feature because you can apply it to any Excel formula.

Enhancements to Office Excel 2007

Excel 2007 provides several enhancements to conditional formatting. These include:

  • Three new visualizations: data bars, color scales, and icon sets that help you to explore large data sets, identify trends and exceptions, and quickly compare data.

  • A new user interface (UI) for adding, removing, and managing conditional formats.

  • Many new conditional formatting rules.

  • The ability to use more than three conditions.

  • The ability to test conditions in addition to "true" or "false."

  • Functionality specific to Microsoft PivotTables.

  • Functionality specific to tables.

In addition to improved conditional formatting, one of the most dramatic changes in the 2007 Microsoft Office system is the new Office Fluent UI, which includes the Ribbon. The Ribbon makes it much easier to find menus, features, and options that you need. And customizing the Ribbon is a breeze. Write a little XML and a little Microsoft Visual Basic for Applications (VBA) code and you're done!

Note

You can easily customize the Ribbon in managed code by using the Microsoft Visual Studio Tools for Office add-in. You can also use unmanaged code, such as VBA code, to create your customization projects. You can implement Ribbon customization by using COM add-ins or by using the new Office Open XML Formats with the 2007 Office system.

The Fluent UI showcases the conditional formatting feature by putting it in a prominent place on the Home tab of the Ribbon in Excel 2007.

Figure 1. Conditional Formatting group in Excel 2007

Creating the Project

In this column, we combine conditional formatting and the Ribbon to create a solution that you can easily extend to custom applications that are more complex. The project adds a custom tab, a custom group, and a button to the Ribbon in an Excel 2007 workbook. When you click the button, conditional formatting is applied to a range of ten values, which highlight the top five percent of the values. In this scenario, the background of the cell containing the largest number is colored red.

To do this, complete the following steps:

  1. Create an XML file containing Ribbon customization code, and then save the file in a folder on the Microsoft Windows desktop.

  2. Create a workbook, and then add a VBA subroutine that sets various properties of the FormatConditions object.

  3. Insert ten different numbers in cells A1 through A10 on Sheet1.

  4. Save the workbook as a macro-enabled file with the extension .xlsm.

  5. Open the file as a compressed (zipped) folder.

  6. Add the folder containing the XML file to the zipped folder.

  7. Modify the relationship file in the folder to point to the XML file.

  8. Remove the .zip extension from the folder name, and then open the file in Excel.

  9. On the custom tab created from the XML file, click the button. The background of the cell that contains the largest number turns red.

Creating the XML Customization File

In this section, you create the XML file that adds a tab, group, and button to the existing Ribbon.

To do this, complete the following steps:

  1. On the Windows desktop, create a folder named customUI.

  2. Using any text editor (such as Notepad), create the customization file by inserting the following XML.

    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <ribbon startFromScratch="false">
      <tabs>
         <tab id="CustomTab" label="Conditional Formatting">
            <group id="SimpleControl" label="Conditional Format">
               <button id="Button1" imageMso="WhatIfAnalysisMenu" size="large"
                  label="Highlight Top 5%"
                  onAction="ThisWorkbook.Top5Percent"/>
            </group>
          </tab>
      </tabs>
   </ribbon>
</customUI>

  3. In the customUI folder, save the file as customUI.xml.

Creating the Macro-Enabled Workbook

In this section, you create the macro-enabled workbook containing the conditional formatting VBA code and the sample data.

To do this, complete the following steps:

  1. Start Excel 2007, and then open a blank workbook. Keep the default workbook name.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    If the Developer tab does not appear in your workbook, do the following:
    1. On the File menu (the large Microsoft Office Button), click Excel Options.
    2. On the Popular tab, select the Show Developer tab in the Ribbon option.
    3. Click OK to close the dialog box.

  3. In the Project pane, double-click the ThisWorkbook node.

  4. In the code window, insert the following procedure.

    Sub Top5Percent()

   'Adding the Top10 rule to the range.
   Range("A1:A10").FormatConditions.AddTop10 

   'Assign the rank of the condition to 5.
   Range("A1:A10").FormatConditions(1).Rank = 5 

   'Set the Percent property to 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 

End Sub

  5. On the File menu, click Save Book1.xlsm.

  6. Close the Visual Basic Editor.

  7. Type the following list of numbers into the worksheet cells.

    Cell

    Number

    A1

    125

    A2

    278

    A3

    41

    A4

    98

    A5

    36

    A6

    157

    A7

    200

    A8

    175

    A9

    239

    A10

    14

  8. Save and close the workbook.

Preparing the Office Open XML Formats File

Now, add the XML customization file to the workbook file and modify the relationships part to point to the XML file.

To do this, complete the following steps:

  1. In Windows Explorer, add the file name extension .zip to the workbook file name, and then double-click the file to open it as a compressed (zipped) folder.

  2. Add the customization file to the container by dragging the customUI folder from the Windows desktop to the compressed folder.

  3. Next, drag the _rels folder from the compressed folder to the Windows desktop.

  4. Open the _rels folder, and then open the .rels file in a text editor.

  5. Between the final <Relationship> tag and the closing </Relationships> tag, add the following line.

    <Relationship Id="customUIRelID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

  6. Save the .rels file.

  7. Drag the .rels file from the Windows desktop to the _rels folder in the compressed folder, replacing the existing file.

Testing the Solution

Finally, open the file in Excel and test the process.

To do this, complete the following steps:

  1. In Windows Explorer, remove the .zip extension from the container file name.

  2. Open the file in Excel 2007.

  3. Click the Conditional Formatting tab.

  4. Click the Highlight Top 5% button. The background of the cell containing the largest number, 278, turns red as shown in the following figure.

    Figure 2. Applying custom conditional formatting

Conclusion

Excel 2007 introduces significant changes to conditional formatting, all of which make it more powerful than ever. In this column, you saw how you can combine two features, conditional formatting and the Ribbon, to create a solution that is seamless to your users. Although the example shown here is simple, you can use these same steps to create options that are more powerful and complex.

In future columns, I will go into detail about additional enhancements to conditional formatting in Excel 2007, such as data bars, color scales, and icon sets.

More About Conditional Formatting in Excel 2007

For more in this series about conditional formatting in Excel 2007, see the following resources: