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.
- Construct a complex range by using the IRangeProvider.Union method of the IRangeProvider object, accessed via the Worksheet.Range property.
- 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.
// 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.