How to: Apply Conditional Formatting to a Complex Range

• Oct 19, 2023
• 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

ConditionalFormatting.cs

// 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));

ConditionalFormatting.vb

' Create a union range to which the rule will be applied.
Dim complexRange As CellRange = worksheet.Range.Union(worksheet("G3:G6"), worksheet("G9:G12"))

Dim conditionalFormattings As ConditionalFormattingCollection = worksheet.ConditionalFormattings
' Specify the automatic minimum value for the shortest bar.
Dim lowBound As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto)
' Specify the automatic maximum value for the longest bar.
Dim highBound As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Auto)
' Create the rule to compare yearly total values for different states. 
Dim cfRule As DataBarConditionalFormatting = conditionalFormattings.AddDataBarConditionalFormatting(complexRange, lowBound, highBound, Color.FromArgb(&H29, &H3E, &H6A))

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