Setting Conditional Formatting in Excel 2007

Summary:  Learn how to apply conditional formatting programmatically in Excel 2007.

Office Visual How To

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

Joel Krist, Akona Systems

April 2007

Overview

Microsoft Office Excel 2007 provides enhanced conditional formatting support. Conditional formatting allows you to apply formatting to one or more cells based on the value of the cell or the value of a formula. This article illustrates how to use the Microsoft Excel 12.0 Object Library to programmatically set conditional formatting on a range of cells using color scales and icon sets.

See It 2007 Excel Setting Conditional Formatting video

Watch the Video

Length: 09:29 | Size: 7.78 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Code Sample

To illustrate how to programmatically set conditional formatting in Excel 2007, this section walks through five steps:

To programmatically set conditional formatting in Excel 2007

  1. Add a reference to the Excel 12.0 Object Library.

  2. Import the Excel interop assembly namespace.

  3. Declare variables.

  4. Create a new workbook and add a worksheet to it.

  5. Implement the conditional formatting.

1. Add a Reference to the Excel 12.0 Object Library

First, add a reference to the Microsoft Excel 12.0 Object Library to the Visual Studio project. To do this, right-click on the project in the Visual Studio Solution Explorer and select the Add Reference… menu item. Select the COM tab in the Add Reference dialog box, then scroll down to the Microsoft Excel 12.0 Object Library component, select it, and then click OK to add the reference.

Figure 1. Adding a Reference

Adding a Reference

2. Import the Excel Interop Namespace

Next, import the Microsoft.Office.Interop.Excel namespace to allow access to the objects defined in the Microsoft Excel 12.0 Object Library without having to specify the full namespace path. Add the following line to the top of the source file.

Imports Microsoft.Office.Interop.Excel
using Microsoft.Office.Interop.Excel;

To import the namespace for Visual Basic projects, right-click the project in the Visual Studio Solution Explorer and select the Properties menu item. On the project properties page select the References tab and then select the checkbox next to the Microsoft.Office.Interop.Excel entry in the list of imported namespaces.

3. Declare Variables

The following block of code declares variables that hold references to the Excel objects used by the conditional formatting code.

Dim excelApplication As ApplicationClass = Nothing
Dim newWorkbook As Workbook = Nothing
Dim targetSheet As Worksheet = Nothing
Dim cfColorScale As ColorScale = Nothing
Dim cfIconSet As IconSetCondition = Nothing
ApplicationClass excelApplication = null;
Workbook newWorkbook = null;
Worksheet targetSheet = null;
ColorScale cfColorScale = null;
IconSetCondition cfIconSet = null;

The following lines of code declare two variables that help to make the parameters passed to methods used in the conditional formatting code easier to read.

Use the paramWorkbookPath variable to specify the path and filename when you save the new Excel 2007 workbook. It is set to C:\Temp\Test.xlsx in the sample code, but you can change this as needed.

Use the paramMissing variable to call methods that accept optional parameters. Optional parameters are only optional when you use Microsoft Visual Basic. You must specify a value for optional parameters when you use Microsoft Visual C#. Using Type.Missing as the value for an optional parameter tells the method being called that the parameter is not being specified and that the method should use the parameter's default value.

Dim paramWorkbookPath As String = "C:\Temp\Test.xlsx"
string paramWorkbookPath = @"C:\Temp\Test.xlsx";
object paramMissing = Type.Missing;

4. Create a New Workbook

The following code creates an instance of Excel 2007 and creates a workbook with one worksheet in it named Conditional Formatting.

excelApplication = New ApplicationClass()
newWorkbook = _
    excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
targetSheet = newWorkbook.Worksheets(1)
targetSheet.Name = "Conditional Formatting"
excelApplication = new ApplicationClass();
newWorkbook = 
    excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
targetSheet = (Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Conditional Formatting";

5. Implement the Conditional Formatting

The following block of code fills cells A1:A10 with sample data and then applies conditional formatting to the data using a two-color color scale. The Visual Basic version of the code uses the built-in RGB function to generate the color values for the minimum and maximum threshold values, whereas the C# version uses hard-coded values for these settings. The reason for this difference is because the RGB function does not exist for C# and the System.Drawing.Color class cannot be used directly to generate the color values in the correct format. The format issue has to do with the difference in the way that the Microsoft .NET Framework and COM represent RGB color values.

In the .NET Framework, colors are represented using the System.Drawing.Color class. The Color class stores a color's RGB component values as an integer in the AARRGGBB format. The two most significant bytes represent the color's alpha value, followed by the red, green, and blue color component values. You can obtain the ARGB value using the Color object's ToArgb() method, such as Color.Red.ToArgb(). However, COM handles colors differently than the .NET Framework does. In COM, colors are represented as an integer in the 00BBGGRR format. The alpha component is 0 and the blue component is in a more significant byte position than the red component. This differs from the .NET Framework.

Because the object model for Excel 2007 is COM-based, the ColorScaleCriterion.FormatColor.Color property expects a color value as an integer with the color components in the 00BBGGRR order. Trying to set the ColorScaleCriterion.FormatColor.Color property to a value returned from a Color object's ToArgb() method does not work because the bytes for the color's red and blue components are swapped. You could create a C# method that would be the equivalent of the Visual Basic RGB function, but for the purposes of this article, the code simply uses hard-coded values.

' Fill cells A1:A10 with sample data.
targetSheet.Range("A1").Value = 1
targetSheet.Range("A2").Value = 2
targetSheet.Range("A1:A2").AutoFill(targetSheet.Range("A1:A10"))

' Create a two-color ColorScale object for the created sample data
' range.
cfColorScale = _
    targetSheet.Range("A1:A10").FormatConditions.AddColorScale(2)

' Set the minimum threshold to red and maximum threshold to blue
cfColorScale.ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
cfColorScale.ColorScaleCriteria(2).FormatColor.Color = RGB(0, 0, 255)
// Fill cells A1:A10 with sample data.
targetSheet.get_Range("A1",
paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 1);
targetSheet.get_Range("A2", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 2);
targetSheet.get_Range("A1:A2",
paramMissing).AutoFill(targetSheet.get_Range("A1:A10", paramMissing), XlAutoFillType.xlFillSeries);

// Create a two-color ColorScale object for the created sample data
// range.
cfColorScale = (ColorScale)(targetSheet.get_Range("A1:A10",
    Type.Missing).FormatConditions.AddColorScale(2));

// Set the minimum threshold to red (0x000000FF) and maximum threshold
// to blue (0x00FF0000).
cfColorScale.ColorScaleCriteria[1].FormatColor.Color = 0x000000FF;
cfColorScale.ColorScaleCriteria[2].FormatColor.Color = 0x00FF0000;

The following block of code fills cells C1:C10 with sample data and then uses the five-arrow icon set to apply conditional formatting to the data.

' Fill cells from C1:C10 with sample data.
targetSheet.Range("C1").Value = 55
targetSheet.Range("C2").Value = 92
targetSheet.Range("C3").Value = 88
targetSheet.Range("C4").Value = 77
targetSheet.Range("C5").Value = 66
targetSheet.Range("C6").Value = 93
targetSheet.Range("C7").Value = 76
targetSheet.Range("C8").Value = 80
targetSheet.Range("C9").Value = 79
targetSheet.Range("C10").Value = 83

' Create an icon set conditional format for the created sample data
' range.
cfIconSet = targetSheet.Range("C1", _
    "C10").FormatConditions.AddIconSetCondition()

' Change the icon set to a 5-arrow icon set.
cfIconSet.IconSet = newWorkbook.IconSets(XlIconSet.xl5Arrows)

' The IconCriterion collection contains all the icon criteria.
' Each criteria can be modified by indexing into the collection.
' The following code sets the criteria for each of the arrows
' in the set.
With cfIconSet.IconCriteria(2)
    .Type = XlConditionValueTypes.xlConditionValueNumber
    .Value = 60
    .Operator = XlFormatConditionOperator.xlGreaterEqual
End With
With cfIconSet.IconCriteria(3)
    .Type = XlConditionValueTypes.xlConditionValueNumber
    .Value = 70
    .Operator = XlFormatConditionOperator.xlGreaterEqual
End With
With cfIconSet.IconCriteria(4)
    .Type = XlConditionValueTypes.xlConditionValueNumber
    .Value = 80
    .Operator = XlFormatConditionOperator.xlGreaterEqual
End With
With cfIconSet.IconCriteria(5)
    .Type = XlConditionValueTypes.xlConditionValueNumber
    .Value = 90
    .Operator = XlFormatConditionOperator.xlGreaterEqual
End With
// Fill cells from C1:C10 with sample data.
targetSheet.get_Range("C1",  paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 55);
targetSheet.get_Range("C2", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 92);
targetSheet.get_Range("C3", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 88);
targetSheet.get_Range("C4", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 77);
targetSheet.get_Range("C5", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 66);
targetSheet.get_Range("C6", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 93);
targetSheet.get_Range("C7", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 76);
targetSheet.get_Range("C8", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 80);
targetSheet.get_Range("C9", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 79);
targetSheet.get_Range("C10", paramMissing).set_Value(XlRangeValueDataType.xlRangeValueDefault, 83);

// Create an icon set conditional format for the created sample data
// range.
cfIconSet = (IconSetCondition)(targetSheet.get_Range("C1", "C10").FormatConditions.AddIconSetCondition());

// Change the icon set to a 5-arrow icon set.
cfIconSet.IconSet = newWorkbook.IconSets[XlIconSet.xl5Arrows];

// The IconCriterion collection contains all the icon criteria.
// Each criteria can be modified by indexing into the collection.
// The following code sets the criteria for each of the arrows in 
// the set.
cfIconSet.IconCriteria[2].Type = XlConditionValueTypes.xlConditionValueNumber;
cfIconSet.IconCriteria[2].Value = 60;
cfIconSet.IconCriteria[2].Operator = (int)(XlFormatConditionOperator.xlGreaterEqual);

cfIconSet.IconCriteria[3].Type = XlConditionValueTypes.xlConditionValueNumber;
cfIconSet.IconCriteria[3].Value = 70;
cfIconSet.IconCriteria[3].Operator = (int)(XlFormatConditionOperator.xlGreaterEqual);

cfIconSet.IconCriteria[4].Type = XlConditionValueTypes.xlConditionValueNumber;
cfIconSet.IconCriteria[4].Value = 80;
cfIconSet.IconCriteria[4].Operator = (int)(XlFormatConditionOperator.xlGreaterEqual);

cfIconSet.IconCriteria[5].Type = XlConditionValueTypes.xlConditionValueNumber;
cfIconSet.IconCriteria[5].Value = 90;
cfIconSet.IconCriteria[5].Operator = (int)(XlFormatConditionOperator.xlGreaterEqual);

Save the Workbook

Save the workbook and exit Excel 2007. The following C# code passes the XlSaveAsAccessMode.xlNoChange value as the seventh parameter to the Workbook.SaveAs method. This is done instead of using the paramMissing variable because the SaveAs method takes an XlSaveAsAccessMode enumerated type rather than an object type for this parameter. For the purposes of this article, the actual value from the XlSaveAsAccessMode enumeration that is passed to the SaveAs method is not important.

After the code saves the Excel 2007 workbook, it closes the workbook and the application objects and releases references to the underlying Excel 2007 COM objects. This allows Excel 2007 to unload from memory. For more information about how to release COM objects when using managed code, see Chapter 2: Basics of Office Interoperability (Part 2 of 3) from Microsoft .NET Development for Microsoft Office.

newWorkbook.SaveAs(paramWorkbookPath)
' Release the references to the Excel objects.
cfIconSet = Nothing
cfColorScale = Nothing
targetSheet = Nothing

' Close the Workbook object.
If Not newWorkbook Is Nothing Then
    newWorkbook.Close(False)
    newWorkbook = Nothing
End If

' Close the ApplicationClass object.
If Not excelApplication Is Nothing Then
    excelApplication.Quit()
    excelApplication = Nothing
End If

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing,
    paramMissing, paramMissing, paramMissing,
    XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, 
    paramMissing, paramMissing, paramMissing);
// Release the references to the Excel objects.
cfIconSet = null;
cfColorScale = null;
targetSheet = null;

// Close the Workbook object.
if (newWorkbook != null)
{
    newWorkbook.Close(false, paramMissing, paramMissing);
    newWorkbook = null;
}

// Close the ApplicationClass object.
if (excelApplication != null)
{
    excelApplication.Quit();
    excelApplication = null;
}

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

The preceding sample code creates a workbook that contains a single worksheet named Conditional Formatting that contains the following data and conditionally formatted cells.

Figure 2. Conditional Formatting

Conditional Formatting

Read It

Conditional formatting makes it simple to highlight specific values or to identify values in a range. For example, conditional formatting can answer requests such as "Show me the top 10 percent of all sales for North America," or "Show me only Nancy's sales for the year." Excel 2007 provides several enhancements to conditional formatting. These include:

  • Three new visualizations that help with exploring large data sets, identifying trends and exceptions, and quickly comparing data: data bars, color scales, and icon sets.

  • A new user interface (UI) to add, remove, and manage 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.

This article explores how to programmatically set conditional formatting in Excel 2007. This next procedure reviews the steps.

To programmatically set conditional formatting in Excel 2007

  1. Add to the project a reference to the Excel 12.0 Object Library. This marks that the project makes use of the Excel 12.0 Object Library.

  2. Import the Microsoft.Office.Interop.Excel namespace. This allows code to use the classes and types exposed as part of the Microsoft.Office.Interop.Excel namespace without having to use the fully qualified namespace path.

  3. Declare variables to hold references to the Excel 2007 objects that are used by the conditional formatting code and make method parameters easier to read.

  4. Create a workbook and add a worksheet to it. This code shows how to create a workbook with a single worksheet in it.

  5. Implement the conditional formatting code. This step shows code that illustrates how to apply color scale and icon set conditional formatting to a range of cells.

Explore It