How to: Use a Formula to Determine which Cells to Format
- 2 minutes to read
This example demonstrates how to create the rule that uses a formula as a criterion to apply a conditional format.
To create a new conditional formatting rule represented by the FormulaExpressionConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddFormulaExpressionConditionalFormatting method. Pass the following parameters:
- A CellRange object that defines a range of cells to which the rule is applied.
- A string value that determines a formula to evaluate.
- Specify formatting options to be applied to cells if the condition is true, using the ISupportsFormatting.Formatting property of the FormulaExpressionConditionalFormatting object.
Transparency is not supported in conditional formatting.
// Create the rule to shade alternate rows without applying a new style. FormulaExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddFormulaExpressionConditionalFormatting(worksheet.Range["$A$2:$G$15"], "=MOD(ROW(),2)=1"); // Specify formatting options to be applied to cells if the condition is true. // Set the background color to light blue. cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
The image below shows the result (the workbook is opened in Microsoft® Excel®). Alternate rows are shaded in light blue without applying a new style.