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.
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.
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?
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
Hello, GRIJESH PRAJAPATI!
If the list of keywords has 2 or more matchable values separated with a comma (,), this will highlight automatically by using the following VBA code.
https://www.exceldemy.com/highlight-specific-text-in-excel-cell-vba/#3_VBA_Code_to_Highlight_Multiple_Specific_Texts_in_a_Range_of_Cells_in_Excel_Case-Insensitive_Match
Any way to also get a selection box for Bold, Italic, Underline, and also for size of text. I.e. If I wanted to make “History” Bold, Red, and size 16. Thanks!
Hello Scott,
Thank you for sharing your problem. As per your query, you can simultaneously format specific text in Bold, Italic, Underline and even change Font Size with the following 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
Selection.Cells(i, j).Characters(k, Len(Text)).Font.Bold = True
Selection.Cells(i, j).Characters(k, Len(Text)).Font.Italic = True
Selection.Cells(i, j).Characters(k, Len(Text)).Font.Underline = True
Selection.Cells(i, j).Characters(k, Len(Text)).Font.Size = 16
End If
Next k
Next j
Next i
End Sub
Apply this code to the selected cells of your dataset and you will get the desired output.
I hope this solution will help you. Waiting to get your feedback.
Regards,
Guria
ExcelDemy