Highlight IF a Cell Contains a Specific Text with Conditional Formatting in Excel

Contents hide

Highlight Cells That Contain a Text in Excel

Highlight Cells That Contain Text using Custom Formula

Use a VBA Code to Highlight Cells with a TEXT

Let’s say you work in a sales department and have an Excel sheet listing customer orders in column A. It’s crucial to quickly identify orders marked as ‘priority’ as they need to be processed first. This ensures that urgent customer needs are met promptly and efficiently.

To make these “priority” orders stand out, you use conditional formatting to highlight any cell in column A containing the word “priority”.

In Excel, just like using a formula, you can use conditional formatting to highlight a cell if it contains a specific text. There are two ways to do this: Pre-Defined Option or Custom Formula. In this tutorial, we are going to learn both methods.

Highlight Cells That Contain a Text in Excel

Below are the simple steps to apply conditional formatting to a cell containing a text you specify. Conditional formatting is the best way to do this:

Select the range on which you want to apply the conditional formatting.
Afterward, go to the Home Tab > Conditional Formatting > Highlight Cells Rule > Text That Contains.
Next, in the dialog box, enter the text you want to look for in the data and select the formatting you want to apply to the matched cells.
In the end, click OK to apply the conditional formatting.

When you click OK, it will highlight all the cells where the text “Data” appears.

Highlight Cells That Contain Text using Custom Formula

The same formatting can be done with the help of a custom formula, and there is one plus point about this method: it applies conditional formatting based on another cell. It makes it a dynamic; you don’t need to open the conditional formatting option to change it.

highlight-cells-contain-text-using-custom-formula

Select the Data and go to the Home Tab > Conditional Formatting > New Rule.
In the new rule dialog box, click on the “Use the formula to determine which cell to format”, and enter this formula in the formula input bar =AND($C$1<>””,SEARCH($C$1,A1)>0)
Next, click “Format…” to specify the format you want to apply to the matched cells.
In the end, click OK in the dialog box, and again OK to apply the formatting.

When you change the text in cell A1, the conditional formatting in the data will change.

change-text-will-change-conditional-formatting

The formula that we have used text two conditions:

If Cell C1 is blank or not.
Is the specified text in the data or not?

Let’s break down the formula to understand it.

$C$1<>”” – It checks if cell C1 is not empty. If C1 contains any text or number, this part returns TRUE.
SEARCH($C$1, A1) – Searches for the content of C1 within cell A1. If the content of C1 is found in A1, SEARCH returns the position of the first character.
SEARCH($C$1, A1)>0 – Check if the SEARCH function found the content of C1 in A1. If found, it returns TRUE; otherwise, it returns FALSE.
AND($C$1<>””, SEARCH($C$1, A1)>0) – Combines the two conditions using AND. It returns TRUE only if both C1 is not empty and the content of C1 is found in A1.

Or you can also use the formula =COUNTIF(A1, “Excel”) > 0 in the conditional formatting to

If you just want to check if a cell contains text, you can use the ISTEXT function. This function tests whether a cell has a number or text and returns TRUE or FALSE, according to the value in the cell.

Use a VBA Code to Highlight Cells with a TEXT

Below is a VBA code that refers to the range you have selected. When you run the code, it asks you for the text you want to highlight. Then, it asks to highlight all the cells in the selected area with a yellow color where that text is.

Sub HighlightCellsContainingText()
    Dim searchText As String
    Dim rng As Range
    Dim firstCell As String
    Dim formatCondition As formatCondition

    ' Get the text from the user
    searchText = InputBox("Enter the text to highlight cells containing this text:", "Highlight Text")

    ' Exit if the user cancels the input box
    If searchText = "" Then Exit Sub

    ' Apply conditional formatting to the selected range
    Set rng = Selection
    firstCell = rng.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

    ' Clear any existing conditional formatting in the selected range
    rng.FormatConditions.Delete

    ' Add new conditional formatting rule
    Set formatCondition = rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=ISNUMBER(SEARCH(""" & searchText & """," & firstCell & "))")

    ' Set the formatting options (e.g., fill color)
    With formatCondition.Interior
        .Color = RGB(255, 255, 0) ' Yellow fill color
    End With
End Sub

To use this code, you need to open the Visual Basic Editor, insert a new module, and paste the code to the code window.