officefileapi-116959-excel-export-library-conditional-formatting-how-to-format-cells-based-on-the-text-in-the-cell.md
The following example demonstrates how to apply a text-based conditional formatting rule.
Create a new XlConditionalFormatting instance that contains formatting rules and settings.
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.
Create a new XlCondFmtRuleSpecificText object that represents the new formatting rule. Pass the following parameters.
Specify the formatting parameters to the cells conforming to the condition.
Add the newly created rule to the corresponding collection contained in the XlConditionalFormatting object by calling the Add method.
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.
// Create an instance of the XlConditionalFormatting class.
XlConditionalFormatting formatting = new XlConditionalFormatting();
// Specify the cell range
// to which the conditional formatting rule should be applied (B2:B7).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 1, 1, 6));
// Create the rule to highlight cells that contain the given text.
XlCondFmtRuleSpecificText rule =
new XlCondFmtRuleSpecificText(XlCondFmtSpecificTextType.Contains, "worldwide");
// Specify formatting settings to be applied to cells
// if the condition is true.
rule.Formatting = XlCellFormatting.Neutral;
formatting.Rules.Add(rule);
// Add the specified format options
// to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting);
' Create an instance of the XlConditionalFormatting class.
Dim formatting As New XlConditionalFormatting()
' Specify the cell range
' to which the conditional formatting rule should be applied (B2:B7).
formatting.Ranges.Add(XlCellRange.FromLTRB(1, 1, 1, 6))
' Create the rule to highlight cells that contain the given text.
Dim rule As
New XlCondFmtRuleSpecificText(XlCondFmtSpecificTextType.Contains, "worldwide")
' Specify formatting settings to be applied to cells
' if the condition is true.
rule.Formatting = XlCellFormatting.Neutral
formatting.Rules.Add(rule)
' Add the specified format options
' to the worksheet collection of conditional formats.
sheet.ConditionalFormattings.Add(formatting)
The following image illustrates the result. The cells with the text that contains the word “worldwide” are formatted using the “Neutral” cell style.