How to: Apply Conditional Formatting to a Complex Range

  • 2 minutes to read

This example demonstrates how to apply a conditional formatting rule to a complex range combination of two non-contiguous ranges.

  1. Construct a complex range by using the IRangeProvider.Union method of the IRangeProvider object, accessed via the Worksheet.Range property.
  2. To apply a data bar conditional formatting rule to the created combined range, access the collection of conditional formats using the Worksheet.ConditionalFormattings property, and call the ConditionalFormattingCollection.AddDataBarConditionalFormatting method with the specified range passed as the first parameter. For details on how to apply a data bar conditional formatting rule, refer to the How to: Format Cells Using Data Bars example.

View Example

// Create a union range to which the rule will be applied.
CellRange complexRange = worksheet.Range.Union(worksheet["G3:G6"], worksheet["G9:G12"]);

ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Specify the automatic minimum value for the shortest bar.
ConditionalFormattingValue lowBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
// Specify the automatic maximum value for the longest bar.
ConditionalFormattingValue highBound = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto);
// Create the rule to compare yearly total values for different states. 
DataBarConditionalFormatting cfRule = conditionalFormattings.AddDataBarConditionalFormatting(complexRange, lowBound, highBound, Color.FromArgb(0x29, 0x3E, 0x6A));

The image below shows the result. The data bars allow you to compare values in the "Yearly Total" column for different states.

SpreadsheetControl_ ConditionalFormatting_ComplexRange

See Also