How to: Format Cells based on the Text in the Cell
- 2 minutes to read
This example demonstrates how to create a rule that applies a conditional format based on the text in a cell.
To create a new conditional formatting rule represented by the TextConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddTextConditionalFormatting method. Pass the following parameters:
- Specify formatting options to be applied to cells if the condition is true using the ISupportsFormatting.Formatting property of the TextConditionalFormatting object.
Transparency is not supported in conditional formatting.
' Create the rule to highlight values with the given text string in cells A2 through A15. Dim cfRule As TextConditionalFormatting = worksheet.ConditionalFormattings.AddTextConditionalFormatting(worksheet.Range("$A$2:$A$15"), ConditionalFormattingTextCondition.Contains, "Bradbury") ' Specify formatting options to be applied to cells if the condition is true. ' Set the background color to pink. cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, &HE1, &H95, &HC2)
The image below shows the result (the workbook is opened in Microsoft® Excel®). In the list of authors, the name "Ray Bradbury" is highlighted in pink.