Excel VBA to Highlight Specific Text in a Cell (6 Ways)

In this article, I’ll show you how you can use Visual Basic Application (VBA) to highlight a specific text within a range of texts in Excel. You’ll learn to highlight a single specific text, as well as multiple specific texts and a range of specific texts.

Download Practice Workbook

Excel VBA to Highlight Specific Text in a Cell

Here we’ve got a data set with the Names, Types, and Prices of some books of a bookshop called Martin Bookstore.

Today, we’ll try to use VBA codes to highlight specific text in this data set.

1. VBA Code to Highlight a Single Specific Text in a Range of Cells in Excel (Case-Insensitive Match)

• First of all, let’s try to develop a Macro to highlight a single specific text from a range of cells in this data set, with a case-insensitive match.
• For example, let’s try to highlight the text “history” from the names of the books.
• Therefore, you can use the following VBA code for this purpose.

⧭ VBA Code:

Sub Highlight_a_Single_Specific_Text_Case_Insensitive()

Text = InputBox("Enter the Specific Text: ")

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        For k = 1 To Len(Selection.Cells(i, j))
            If LCase(Mid(Selection.Cells(i, j), k, Len(Text))) = LCase(Text) Then
                Selection.Cells(i, j).Characters(k, Len(Text)).Font.ColorIndex = Color_Code
            End If
        Next k
    Next j
Next i

End Sub

⧭ Step-by-Step Procedure to Run the Code:

Step 1: Opening the VBA Window

• Press ALT + F11 on your keyboard. Instantly, the VBA window will open.

Step 2: Inserting a New Module

• Now, Go to the Insert > Module option in the VBA toolbar.
• Then, click on Insert. A new module called “Module 1” will be inserted.

Step 3: Entering the VBA Code

• Afterward, then paste the given VBA code into the new module.

Step 4: Saving Macro-Enabled Workbook

• Further, save the workbook as Excel Macro-Enabled Workbook.

Step 5: Running the VBA Code

• Now, come back to your worksheet and select the range where you want to highlight the specific text. Here I’ve selected the column Book Name.
• Then, press ALT+F8 on your keyboard. A dialogue box called Macro will open.
• After that, select Highlight_a_Single_Specific_Text_Case_Insensitive and click on Run.

Step 6: Entering the Inputs

• Consecutively, you will get two Input boxes. The first box will ask you to enter the specific text to highlight. Here, I’ve inserted “History”.

• Also, the 2nd box will ask you to enter the color in which you want to highlight the text.
• Enter 3 for a red, 5 for a blue, 6 for a yellow and 10 for green or enter any other color code according to the table provided by Microsoft.
• Here, I’ve entered 3 (Red).

Step 7: The Final Output

• Finally, click on OK. You’ll get all the instances of the text “History” highlighted in red in your selected range.

The code is case-insensitive. So, along with “history”, “History” and all others will also be highlighted.

Read More: How to Highlight Selected Text in Excel (8 Ways)

2. VBA Code to Highlight a Single Specific Text in a Range of Cells in Excel (Case-Sensitive Match)

Again, let’s try to develop a Macro to highlight a single specific text in a range of Cells in Excel, but this time for a case-sensitive match.

We’ll again try to highlight the text “History” from the names of the books.

• Therefore, you can use this VBA code this time.

⧭ VBA Code:

Sub Highlight_a_Single_Specific_Text_Case_Sensitive()

Text = InputBox("Enter the Specific Text: ")

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        For k = 1 To Len(Selection.Cells(i, j))
            If Mid(Selection.Cells(i, j), k, Len(Text)) = Text Then
                Selection.Cells(i, j).Characters(k, Len(Text)).Font.ColorIndex = Color_Code
            End If
        Next k
    Next j
Next i

End Sub

⧭ Steps to Run the Code:

• The steps to run the code are the same as above.
• So, create a new module and insert the code there.

• Then, save the workbook as Excel Macro-Enabled Workbook, come back to your worksheet, select the range of cells, and run the Macro called Highlight_a_Single_Specific_Text_Case_Sensitive.

• Consecutively, you’ll get two input boxes. Enter the specific text (“History”) in the 1st box.

• Also, type the color code (3 in this example) in the 2nd box.

• Then, click OK. You’ll find the instances of the text “history” highlighted red in your worksheet.

This is a case-sensitive code. So only the text “history” will be marked, not “History” or anything else.

Read More: Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)

3. VBA Code to Highlight Multiple Specific Texts in a Range of Cells in Excel (Case-Insensitive Match)

Up till now, we’ve developed Macros to highlight only a single specific text. Now, we’ll try to highlight multiple specific texts.

For example, let’s try to highlight the texts “History”, “Crime” from the names of the books.

We’ll try for a case-insensitive match first.

• Therefore, you can use the following VBA code:

⧭ VBA Code:

Sub Highlight_Multiple_Specific_Texts_Case_Insensitive()

Texts = InputBox("Enter the Specific Texts (Separated by Commas. No Space after the Commas): ")

Texts = Split(Texts, ",")

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        For k = 1 To Len(Selection.Cells(i, j))
            For l = 0 To UBound(Texts)
                If LCase(Mid(Selection.Cells(i, j), k, Len(Texts(l)))) = LCase(Texts(l)) Then
                    Selection.Cells(i, j).Characters(k, Len(Texts(l))).Font.ColorIndex = Color_Code
                End If
            Next l
        Next k
    Next j
Next i

End Sub

⧭ Steps to Run the Code:

• The steps to run the code are also the same as above.
• So, create a new module and insert the code there.

• Then, save the workbook as Excel Macro-Enabled Workbook.
• Afterward, come back to your worksheet, select the range of cells, and run the Macro called Highlight_Multiple_Specific_Texts_Case_Insensitive.

• Consecutively, you’ll get two input boxes as always. In the 1st box, enter the specific texts separated by commas (No space after the commas).
• Here, I’ve entered history,crime.

• Again, enter the color code (10 in this example) in the 2nd box.

• Then, click OK. You’ll find the instances of the texts “history”, “crime” and “love” highlighted green in your worksheet.

This is a case-insensitive code. So “history”, “History”, “crime”, “Crime” have been marked.

Similar Readings

• How to Highlight from Top to Bottom in Excel (5 Methods)
• Compare Two Excel Sheets and Highlight Differences (7 Ways)
• How to Highlight a Row in Excel (2 Effective Ways)
• Highlight Cells Based on Text in Excel [2 Methods]

4. VBA Code to Highlight Multiple Specific Texts in a Range of Cells in Excel (Case-Sensitive Match)

Now, it’s time for the case-sensitive match. Let’s try to develop a Macro to highlight some specific texts in a range of cells with a case-sensitive match.

• We’ll again try to highlight the texts “History”, “Crime”.

⧭ VBA Code: 

Sub Highlight_Multiple_Specific_Texts_Case_Sensitive()

Texts = InputBox("Enter the Specific Texts (Separated by Commas. No Space after the Commas): ")

Texts = Split(Texts, ",")

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

For i = 1 To Selection.Rows.Count
    For j = 1 To Selection.Columns.Count
        For k = 1 To Len(Selection.Cells(i, j))
            For l = 0 To UBound(Texts)
                If Mid(Selection.Cells(i, j), k, Len(Texts(l))) = Texts(l) Then
                    Selection.Cells(i, j).Characters(k, Len(Texts(l))).Font.ColorIndex = Color_Code
                End If
            Next l
        Next k
    Next j
Next i

End Sub

⧭ Steps to Run the Code:

The steps to run the code are also the same.

• Therefore, create a new module and insert the code there.

• Then, save the workbook as Excel Macro-Enabled Workbook, come back to your worksheet, select the range of cells, and run the Macro called Highlight_Multiple_Specific_Texts_Case_Sensitive.

• Again, the two input boxes. In the 1st box, enter the specific texts separated by commas (No space after the commas). Here I’ve entered history,crime same as the earlier.

• Also, type the color code (5 in this example) in the 2nd box.

• Then, click OK. You’ll find the instances of the texts “history”, “crime” and “love” highlighted blue in your worksheet.

This is a case-sensitive code. So only “history”, “crime”, and “love” have been marked. Nothing else.

5. VBA Code to Highlight a Range of Specific Texts in a Range of Cells in Excel (Case-Insensitive Match)

Finally, we’ll try to highlight a range of specific texts in a range of cells. Look at this new data set.

We have an extra column called Texts to Highlight. We’ll match each text value from this column with the corresponding text value of the Book Name column. Also, highlight the portion in the book name if the corresponding text value is found in the name.

So, let’s develop the Macro now. Firstly, for the case-insensitive match.

⧭ VBA Code:

Sub Highlight_Range_of_Specific_Texts_Case_Insensitive()

Rng = InputBox("Enter the Range of the Specific Texts: ")

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

Dim Start As Integer
Start = 1

For Each i In Range(Rng)
    For j = 1 To Len(Selection.Cells(Start, 1))
        If LCase(Mid(Selection.Cells(Start, 1), j, Len(i))) = LCase(i) Then
            Selection.Cells(Start, 1).Characters(j, Len(i)).Font.ColorIndex = Color_Code
        End If
    Next j
    Start = Start + 1
Next i

End Sub

⧭ Steps to Run the Code:

The steps to run the code are the same.

• Therefore, create a new module and insert the code there.

• Then, save the workbook as Excel Macro-Enabled Workbook, come back to your worksheet, select the range of cells, and run the Macro called Highlight_Range_of_Specific_Texts_Case_Insensitive.

• Afterward, the two input boxes will appear. In the 1st box, enter the range of the specific texts to match. In this example, it’s F5:F14.

• Also, write the color code (3 in this example) in the 2nd box.

• Then, click OK. You’ll find the instances highlighted in red where any portion of the book name matches the corresponding text value (With case-insensitive match).

Read More: Excel VBA to Highlight Cell Based on Value (5 Examples)

6. VBA Code to Highlight a Range of Specific Texts in a Range of Cells in Excel (Case-Sensitive Match)

You can execute a similar task for the case-sensitive match too. The VBA code will be:

⧭ VBA Code:

Sub Highlight_Range_of_Specific_Texts_Case_Sensitive()

Rng = InputBox("Enter the Range of the Specific Texts: ")

Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))

Dim Start As Integer
Start = 1

For Each i In Range(Rng)
    For j = 1 To Len(Selection.Cells(Start, 1))
        If Mid(Selection.Cells(Start, 1), j, Len(i)) = i Then
            Selection.Cells(Start, 1).Characters(j, Len(i)).Font.ColorIndex = Color_Code
        End If
    Next j
    Start = Start + 1
Next i

End Sub

• Therefore, the output will be like this:

• This time, only the case-sensitive matches will be highlighted.

Additional Reading

Here I’ve shown how you can color only the specific text in a cell that matches. But if you want to color the complete cell background, use Conditional Formatting instead.

Conclusion

So, using these methods, you can highlight a specific text in a cell using VBA in Excel. Do you have any questions? Feel free to ask us.

Related Readings

• How to Highlight Text in Text Box in Excel (3 Handy Ways)
• Excel Cell Color: Add, Edit, Use & Remove
• How to Highlight Partial Text in Excel Cell (9 Methods)
• VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)
• How to Highlight a Cell in Excel (5 Methods)