ConditionalFormattingCollection.AddFormulaExpressionConditionalFormatting(CellRange, String) Method
Applies the conditional formatting rule that uses a formula to determine which cells to format.
A CellRange object that specifies a range of cells to which the conditional formatting rule is applied.
A String value that represents a formula in the invariant culture to be used as a formatting criteria. Start the formula with an equal sign (=). The formula can include the spreadsheet’s built-in functions. You can combine relative and absolute references to adjust the formula in each cell if necessary.
A FormulaExpressionConditionalFormatting object that represents the specified conditional format.
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.
' Create the rule to shade alternate rows without applying a new style. Dim cfRule As FormulaExpressionConditionalFormatting = 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, &HBC, &HDA, &HF7)