Use the Excel Export API to Use a Formula to Determine what Cells to Format

• Sep 19, 2023
• 3 minutes to read

The following example demonstrates how to use a formula as a formatting criterion.

1. Create a new XlConditionalFormatting XlConditionalFormatting instance that contains formatting rules and settings.
2. Specify the cell range to which the formatting is going to be applied. To do that, add the target range to the ranges collection, accessible through the XlConditionalFormatting.Ranges property.
3. Create a new XlCondFmtRuleExpression object with the passed string formula that will be used as a rule criterion.

4. Specify the formatting parameters to cells conforming to the condition.

   • To use one of the built-in cell styles, set the XlCondFmtRuleWithFormatting.Formatting property to the corresponding XlCellFormatting enumeration value.
   • To apply a custom background color, use the XlFormatting.Fill property or use one of the static XlFill methods as a Formatting property value. The XlFormatting.Font property allows you to set the desired font parameters (color, size, etc.).
5. Add the newly created rule to the corresponding collection contained in the XlConditionalFormatting object by calling the Add method.
6. To activate the created conditional formatting rule, add the object created in step 1 to the worksheet collection of conditional formatting rules. The collection can be accessed through the IXlSheet.ConditionalFormattings property.

View Example

ConditionalFormattingActions.cs

// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();

// Specify the cell range
// to which the conditional formatting rules should be applied (A2:C7).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 1, 2, 6));

// Create the rule that uses a formula to highlight cells
// if a value in the column "C" is greater than 0 and less than 50. 
XlCondFmtRuleExpression rule = new XlCondFmtRuleExpression("AND($C2>0,$C2<50)");

// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlFill.SolidFill(Color.FromArgb(0xff, 0xff, 0xcc));
formatting.Rules.Add(rule);

// Create the rule that uses a formula to highlight cells
// if a value in the column "C" is less than or equal to 0. 
rule = new XlCondFmtRuleExpression("$C2<=0");

// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Bad;
formatting.Rules.Add(rule);

// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);

ConditionalFormattingActions.vb

' Create an instance of the XlConditionalFormatting class.
Dim formatting As New XlConditionalFormatting()

' Specify the cell range
' to which the conditional formatting rules should be applied (A2:C7).
formatting.Ranges.Add(XlCellRange.FromLTRB(0, 1, 2, 6))

' Create the rule that uses a formula to highlight cells
' if a value in the column "C" is greater than 0 and less than 50. 
Dim rule As New XlCondFmtRuleExpression("AND($C2>0,$C2<50)")

' Specify formatting settings to be applied to cells
' if the condition is true.
rule.Formatting = XlFill.SolidFill(Color.FromArgb(&Hff, &Hff, &Hcc))
formatting.Rules.Add(rule)

' Create the rule that uses a formula to highlight cells
' if a value in the column "C" is less than or equal to 0.
rule = New XlCondFmtRuleExpression("$C2<=0")

' Specify formatting settings to be applied to cells
' if the condition is true.
rule.Formatting = XlCellFormatting.Bad
formatting.Rules.Add(rule)

' Add the specified format options
' to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting)

The following image demonstrates the result.