Conditional Formatting in Spreadsheet for WPF
- 5 minutes to read
Conditional formatting is used to highlight important information and make data interpretation easier. Conditional formats such as cell background color, border line style or font color can be applied to cells whose values meet a certain condition specified by a conditional formatting rule.
Warning
Transparency is not supported in conditional formatting.
All conditional formatting rules specified in a worksheet are stored in the ConditionalFormattingCollection collection, accessible from the Worksheet.ConditionalFormattings property. The table below lists the available types of conditional formatting rules.
To create this rule | Do the following | Example |
---|---|---|
Average This image shows price data with conditional formatting, highlighting values that are above the average in the range of cells. | Call the Conditional | How to: Format Cell Values that are Above or Below the Average |
Color This image shows price distribution using a gradation of two colors. Blue represents the lower values and yellow represents the higher values. | Use the Conditional | |
Color This image shows price distribution using a gradation of three colors. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values. | Use the Conditional | |
Data This image shows markup magnitude using data bars. | Use the Conditional | |
Icon This image shows upward and downward cost trends. | Use the Conditional | |
Expression This image shows cost data with conditional formatting, highlighting values that are greater than $18. | Call the Conditional | How to: Format Cells that are Less than, Greater than or Equal to a Value |
Formula This image shows a data table with conditional formatting that uses the MOD | Call the Conditional | |
Range This image shows price data with conditional formatting, highlighting values that are less than $10 and greater than $19. | Call the Conditional | How to: Format Cells that are Between or Not Between Two Values |
Rank This image shows price data with conditional formatting, highlighting the top three values. | Call the Conditional | |
Text This image shows a list of authors in which the name Ray Bradbury is highlighted. | Call the Conditional | |
Special This image shows a list of authors in which the unique values are highlighted. | Call the Conditional | How to: Format Unique or Duplicate Values, Blank Cells and Formula Errors |
Time This image shows a table with conditional formatting that highlights today’s date. | Call the Conditional |
All of these objects inherit from the ConditionalFormatting base interface, which provides basic properties to specify a conditional formatting rule:
- the ConditionalFormatting.Priority property, which determines the evaluation order for multiple conditional formatting rules in a worksheet;
- the ConditionalFormatting.StopIfTrue property, which specifies whether rules with lower priority can be applied;
- the ConditionalFormatting.Range property, which specifies the contiguous or non-contiguous range (composed of multiple ranges by using the IRangeProvider.Union or CellRange.Union method) to which the conditional format is applied.
To remove the conditional formatting rule from the collection, use the ConditionalFormattingCollection.Remove or ConditionalFormattingCollection.RemoveAt method. To remove all conditional formatting rules from a worksheet, use the ConditionalFormattingCollection.Clear method.