How to: Format Cells that are Between or Not Between Two Values
- 2 minutes to read
This example demonstrates how to apply the conditional formatting rule, highlighting cells that are between or not between two values.
To create a new conditional formatting rule represented by the RangeConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddRangeConditionalFormatting method. Pass the following parameters:
- A CellRange object that defines a range of cells to which the rule is applied.
- A condition specified by one of the ConditionalFormattingRangeCondition enumeration values.
- A string representing the low bound of the data subset inside or outside of which, formatted cells are located. Notice that the string can determine a formula to evaluate the lowest value.
- A string representing the high bound of the data subset inside or outside of which, formatted cells are located. Notice that the string can determine a formula to evaluate the highest value.
- Specify formatting options to be applied to cells if the condition is true using the ISupportsFormatting.Formatting property of the RangeConditionalFormatting object. Set the background and font colors.
A complete sample project is available at https://github.com/DevExpress-Examples/how-to-apply-conditional-formatting-to-a-range-of-cells-e4959
// Create the rule to identify values below 7 and above 19 in cells F2 through F15. RangeConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddRangeConditionalFormatting(worksheet["$F$2:$F$15"], ConditionalFormattingRangeCondition.Outside, "7", "19"); // Specify formatting options to be applied to cells if the condition is true. // Set the background color to yellow. cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA); // Set the font color to red. cfRule.Formatting.Font.Color = Color.Red;
The image below shows the result (the workbook is opened in Microsoft® Excel®). Price values below $7 and above $19 are colored in yellow, and the font color is changed to red.