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.

Data Set to Highlight Specific Text in a Cell with VBA in Excel

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.

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

⧪ Note: This code produces a Macro called Highlight_a_Single_Specific_Text_Case_Insensitive.

Step by Step Procedure to Run the Code:

Step 1: Opening the VBA Window

Press ALT+F11 on your keyboard. The VBA window will open.

Opening VBA Window to Highlight Specific Text in a Cell with VBA in Excel

Step 2: Inserting a New Module

Go to the Insert > Module option in the VBA toolbar.

Click on Insert. A new module called “Module 1” will be inserted.

Step 3: Entering the VBA Code

Then paste the given VBA code in the new module.

VBA Code to Highlight Specific Text in a Cell with VBA in Excel

Step 4: Saving Macro-Enabled Workbook

Save the workbook as Excel Macro-Enabled Workbook.

Saving the Workbook to Highlight Specific Text in a Cell with VBA in Excel

Step 5: Running the VBA Code

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. Select Highlight_a_Single_Specific_Text_Case_Insensitive and click on Run.

Step 6: Entering the Inputs

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

Entering Input to Highlight Specific Text in a Cell with VBA in Excel

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 this table provided by Microsoft.

Here I’ve entered 3 (Red).

Step 7: The Final Output

Click on OK. You’ll get all the instances of the text “history” highlighted in red in your selected range.

Output to Highlight Specific Text in a Cell with VBA in Excel

[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.

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

⧪ Note: This code produces a Macro called Highlight_a_Single_Specific_Texts_Case_Sensitive.

Steps to Run the Code:

The steps to run the code are the same as above. Create a new module and insert the code there.

VBA Code to Highlight Specific Text in a Cell with VBA in Excel

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.

Running Macro to Highlight Specific Text in a Cell with VBA in Excel

You’ll get two input boxes. Enter the specific text (“history”) in the 1st box.

And the color code (3 in this example) in the 2nd box.

Entering Inputs to Highlight Specific Text in a Cell with VBA in Excel

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

Output to Highlight Specific Text in a Cell with VBA in Excel

[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”, and “love” from the names of the books.

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

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

⧪ Note: This code produces a Macro called Highlight_Multiple_Specific_Texts_Case_Insensitive.

Steps to Run the Code:

The steps to run the code are also the same as above. 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_Insensitive.

Running Macro to Highlight Specific Text in a Cell with VBA in Excel

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,love.

Entering Input to Highlight Specific Text in a Cell with VBA in Excel

And the color code (3 in this example) in the 2nd box.

Entering Input to Highlight Specific Text in a Cell with VBA in Excel

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

[This is a case-insensitive code. So “history”, “History”, “crime”, “Crime”, “love”, “Love” all have been marked.]

Similar Readings


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” and “love”.

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

⧪ Note: This code produces a Macro called Highlight_Multiple_Specific_Texts_Case_Sensitive.

Steps to Run the Code:

The steps to run the code are also the same. Create a new module and insert the code there.

VBA Code to Highlight Specific Text in a Cell with VBA in Excel

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.

Running Macro to Highlight Specific Text in a Cell with VBA in Excel

Then 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,love, same as the earlier.

Entering Input to Highlight Specific Text in a Cell with VBA in Excel

And the color code (3 in this example) in the 2nd box.

Entering Input to Highlight Specific Text in a Cell with VBA in Excel

Then click OK. You’ll find the instances of the texts “history”, “crime” and “love” highlighted red 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.

New Data Set to Highlight Specific Text in a Cell with VBA in Excel

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.

And highlight the portion in the book name if the corresponding text value is found in the name.

So, let’s develop the Macro now.

First, 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

⧪ Note: This code produces a Macro called Highlight_Range_of__Specific_Text_Case_Insensitive.

Steps to Run the Code:

The steps to run the code are the same. Create a new module and insert the code there.

VBA Code to Highlight Specific Text in a Cell with VBA in Excel

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.

Then the two input boxes. In the 1st box, enter the range of the specific texts to match. In this example, it’s F4:F13.

Entering Input to Highlight Specific Text in a Cell with VBA in Excel

And the color code (3 in this example) in the 2nd box.

Entering Input to Highlight Specific Text in a Cell with VBA in Excel

Then click OK. You’ll find the instances are 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
<img class="alignnone size-full wp-image-52036" src="https://www.exceldemy.com/wp-content/uploads/2021/12/highlight-specific-text-in-excel-cell-vba-28.png" alt="" width="716" height="451" />

⧪ Note: This code produces a Macro called Highlight_Range_of__Specific_Texts_Case_Sensitive.

After running the code, the output will be like this:

Output to Highlight Specific Text in a Cell with VBA in Excel

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

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

6 Comments
  1. how to insert the requested “text” & requested “color code” in the code itself .. without need of selection message box

    • Hi Mohammed, Thanks for your response. Insert the name of the text and the color code in the 2nd and 3rd lines of the codes directly.

  2. VBA code number 3 and 5 doesn’t work. I needed these codes but when I tried, 3rd one hangs and 5th one doesn’t work. Can you help

    • Hi Arjun, the codes are absolutely flawless and there is no reason for them to not work properly. Did you insert the inputs correctly?

  3. Reply
    Grijesh Prajapati Apr 19, 2022 at 9:32 PM

    HI,

    The above given VBA was very helpful,
    Only one thing, if the list of keywords has 2 or more matchable values separated with comma (,),
    How it can be the highlight

Leave a reply

ExcelDemy
Logo