Note: The other languages of the website are Google-translated. Back to English
English English

How to highlight specific text within a cell based on other text?

In Excel, it may be easy for us to highlight the cells based on a specific text, but, here, I want to highlight a specific text within a cell to make it outstanding but not the entire cell. This maybe a troublesome for most of us. This article, I will talk about some tricks for solving this job in Excel.


Highlight one or more specific text within multiple cells with VBA code

For example, I have a range of text strings, and now, I want to highlight the specific text “Sky” in these cells to get the result as following screenshots shown:

To highlight only part of text within a cell, the following VBA code can help you.

1. Select the cells which you want to highlight the specific text, and then hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Highlight a part of text within a cell:

Sub HighlightStrings()
'Updateby Extendoffice
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
cFnd = InputBox("Enter the text string to highlight")
y = Len(cFnd)
For Each Rng In Selection
  With Rng
    m = UBound(Split(Rng.Value, cFnd))
    If m > 0 Then
      xTmp = ""
      For x = 0 To m - 1
        xTmp = xTmp & Split(Rng.Value, cFnd)(x)
        .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
        xTmp = xTmp & cFnd
      Next
    End If
  End With
Next Rng
Application.ScreenUpdating = True
End Sub

3. Then press F5 key to run this code, and a prompt box will pop out to remind you enter the text that you want to highlight only, see screenshot:

4. And then click OK button, all the text you specified has been highlighted only within the cells, see screenshot:

Tips: If you need to highlight multiple keywords from the text strings, please apply the below code:
VBA code: Highlight multiple keywords from text strings:
Sub HighlightStrings()
'Updateby Extendoffice
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
Dim xFNum As Integer
Dim xArrFnd As Variant
Dim xStr As String
cFnd = InputBox("Please enter the text, separate them by comma:")
If Len(cFnd) < 1 Then Exit Sub
xArrFnd = Split(cFnd, ",")
For Each Rng In Selection
With Rng
For xFNum = 0 To UBound(xArrFnd)
xStr = xArrFnd(xFNum)
y = Len(xStr)
m = UBound(Split(Rng.Value, xStr))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(Rng.Value, xStr)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & xStr
Next
End If
Next xFNum
End With
Next Rng
Application.ScreenUpdating = True
End Sub

Then, in the popped out box, please enter the keywords that you want to highlight,(separate the words with a comma), see screenshot:

And then, click OK button,the specified words have been highlighted at once, see screenshots:

Note: The above codes are case sensitive.


Highlight one or more specific text within multiple cells with an amazing feature

If you are not familiar with the code in Excel, here, i will introduce an easy tool - Kutools for Excel, with its Mark Keyword feature, you can highlight the specific one or more keywords at once within the cells.

Note:To apply these Mark Keyword features, firstly, you should download the Kutools for Excel, and then apply the features quickly and easily.

After installing Kutools for Excel, please do as follows:

1. Click Kutools > Text > Mark Keyword, see screenshot:

2. In the Mark Keyword dialog box, please do the following operations:

  • Select the data range that you want to use from the Range textbox;
  • Select the cells contains the keywords that you want to highlight, you can also enter the keywords manually (separate by comma) into the Keyword text box
  • At last, you should specify a font color for highlighting the texts by check Mark keyword colors option. (To color the entire cells containing the keywords, select the Mark the cell content colors option)

3. Then, click Ok button, all specified texts have been highlighted as below screenshot shown:

Note: This feature is not case sensitive, if you want to highlight the text with case sensitive, please check Match Case in the Mark Keyword dialog box.


Highlight specific text within a cell based on other text with VBA code

Here is another situation, I have two columns which the first column contains the text strings and the second column is the specific text, now, I need to highlight the relative text in the first column based on the specific text in the second column for each row.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Highlight a part of text within a cell based on other text:

Sub highlight()
'Updateby Extendoffice
    Dim xStr As String
    Dim xRg As Range
    Dim xTxt As String
    Dim xCell As Range
    Dim xChar As String
    Dim I As Long
    Dim J As Long
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      xTxt = ActiveWindow.RangeSelection.AddressLocal
    Else
      xTxt = ActiveSheet.UsedRange.AddressLocal
    End If
LInput:
    Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    If xRg.Areas.Count > 1 Then
        MsgBox "not support multiple columns"
        GoTo LInput
    End If
    If xRg.Columns.Count <> 2 Then
        MsgBox "the selected range can only contain two columns "
        GoTo LInput
    End If
    For I = 0 To xRg.Rows.Count - 1
        xStr = xRg.Range("B1").Offset(I, 0).Value
        With xRg.Range("A1").Offset(I, 0)
            .Font.ColorIndex = 1
            For J = 1 To Len(.Text)
                If Mid(.Text, J, Len(xStr)) = xStr Then .Characters(J, Len(xStr)).Font.ColorIndex = 3
            Next
        End With
    Next I
End Sub

3. After pasting the code, and press F5 key to run it, a prompt box will pop out to remind you select the data range which both contains the text string and specific text you want to highlight and based on, see screenshot:

4. And then click OK button, all the corresponding text in the first column based on the specific text in the second column has been colored red as following screenshot:


More relative articles:

  • Bold Part Text When Concatenate Two Columns In Excel
  • In Excel worksheet, after concatenating two cell values with formulas, you may find it will not bold part of the text in the combined formula cell. This may be annoying in sometimes, how could you bold part text when concatenating two columns in Excel?
  • Concatenate Cell Columns And Keep Text Color In Excel
  • As we all known, while concatenating or combining cell columns into one column, the cell formatting (such as text font color, number formatting, etc) will be lost. This article, I will introduce some tricks to combine the cell columns into one and keep the text color as easily as possible in Excel.
  • Display Specific Text Based On Values In Another Column
  • Supposing, I have a list of numbers, now, I want to display some specific text in another column based on this column numbers. For example, if the cell number is between 1-100, I want the text “Decrease” is displayed in adjacent cell, if the number is between 101-200, a text “Stable” is displayed, and if the number larger than 200, a text “Increase” is displayed as following screenshot shown. To solve this task in Excel, the following formulas in this article may help you.
  • Sum Cells With Text And Numbers In Excel
  • For example, I have a list of values containing numerical and textual strings, now, I want to sum only the numbers based on the same text, look at the following screenshot. Normally, you can’t sum the values in the list with text string directly, here, I will introduce you some formulas to deal with this task.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (39)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you so much for your tip and macros. I can't imagine what I would do without them.
This comment was minimized by the moderator on the site
I have checked both given codes in excel 2016 but not working.
This comment was minimized by the moderator on the site
I used Excel365. First VBA is not working. Second one cannot highlight correct keywords i.e. not working too
This comment was minimized by the moderator on the site
Thank you!!!
This comment was minimized by the moderator on the site
I get an error and when I debug it highlights this section m = UBound(Split(Rng.Value, cFnd))
This comment was minimized by the moderator on the site
It worked great for me, thank you for sharing this with the world.
This comment was minimized by the moderator on the site
Can anyone help me for a code to " copy those highlighted word in a different column ".
This comment was minimized by the moderator on the site
how could i get this code to work but have it highlight in a color other than red?
This comment was minimized by the moderator on the site
Hello,
If you want to highlight the text with other color, you just need to change the color index number 3 to other color index number in the below script:
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3

Please try it. Thank you!
This comment was minimized by the moderator on the site
Hello,
With same macro i want to highlight some other text with the different color ex : Green,please help me with this
This comment was minimized by the moderator on the site
Hi, Sukumar,
Sorry, can't solve your problem.
Please comment here if any other has the solution.
Thank you!
This comment was minimized by the moderator on the site
Thank you for "Highlight A Specific Text Within Multiple Cells With VBA Code" It works great. Would you please explain:
line 18 xTmp = xTmp & Split(Rng.Value, cFnd)(x)
Why idoes the "(x)" have to appear where it is? Is the value of x passed to the Split function as a named argument without the name? I would really like to understand this concept.Thank you for your help and this function. I have learned something I didn't know.
This comment was minimized by the moderator on the site
hi, i noticed that this is case senitive highliter. Where can i change that it would highlite lower and upper casese in deired word
This comment was minimized by the moderator on the site
Hello, lolster,

In the above two codes, which code do you want to identify case sensitive?
We will modify the code for you!
This comment was minimized by the moderator on the site
Hi, I would like to ask the same question to highlight both lower and upper case, in the case of below:

"find and mark keyword1, keyword2 and keyword3 in all text strings also when one string contains more than one of these keywords (all of them should get highlighted in the specific text string)"
This comment was minimized by the moderator on the site
Sub HighlightStrings()
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
Dim xFNum As Integer
Dim xArrFnd As Variant
Dim xStr As String
cFnd = InputBox("Please enter the text, separate them by comma:")
If Len(cFnd) < 1 Then Exit Sub
xArrFnd = Split(cFnd, ",")
For Each Rng In Selection
With Rng
For xFNum = 0 To UBound(xArrFnd)
xStr = xArrFnd(xFNum)
y = Len(xStr)
m = UBound(Split(Rng.Value, xStr))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(Rng.Value, xStr)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & xStr
Next
End If
Next xFNum
End With
Next Rng
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Hello,Mahendra
If you want the code not case sensitive, please apply the below code:
Sub HighlightStrings()
'Updateby Extendoffice
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
Dim xFNum As Integer
Dim xArrFnd As Variant
Dim xStr As String
cFnd = InputBox("Please enter the text, separate them by comma:")
If Len(cFnd) < 1 Then Exit Sub
xArrFnd = Split(UCase(cFnd), ",")
For Each Rng In Selection
With Rng
For xFNum = 0 To UBound(xArrFnd)
xStr = xArrFnd(xFNum)
y = Len(xStr)
m = UBound(Split(UCase(Rng.Value), UCase(xStr)))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(UCase(Rng.Value), UCase(xStr))(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & xStr
Next
End If
Next xFNum
End With
Next Rng
Application.ScreenUpdating = True
End Sub


Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Is there a way to make the first VBA code in a way to work with a fixed list of keywords without a promt box? For example, I always want to find and mark keyword1, keyword2 and keyword3 in all text strings also when one string contains more than one of these keywords (all of them should get highlighted in the specific text string). And is it possible to make keyword1 and keyword2 red but the third one just bold?
This comment was minimized by the moderator on the site
Hello, Arturs,

To highlight multiple keywords from a cell, the following vba code can help you:(you should separate the words by semicolon in the popped out input box)

Sub HighlightStrings()
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
Dim xFNum As Integer
Dim xArrFnd As Variant
Dim xStr As String
cFnd = InputBox("Please enter the text, separate them by semicolon:")
If Len(cFnd) < 1 Then Exit Sub
xArrFnd = Split(cFnd, ";")
For Each Rng In Selection
With Rng
For xFNum = 0 To UBound(xArrFnd)
xStr = xArrFnd(xFNum)
y = Len(xStr)
m = UBound(Split(Rng.Value, xStr))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(Rng.Value, xStr)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & xStr
Next
End If
Next xFNum
End With
Next Rng
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Hello Skyyang! I found there would be an error if running the code - it could not specify the word following a colon. Do you have any suggestion to improve it? Thank you very much!
This comment was minimized by the moderator on the site
Hello, IvyCY,
If you want to specify the words by a colon, you just need to change the semicolon symbol to colon, please apply the following code:

Sub HighlightStrings()
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
Dim xFNum As Integer
Dim xArrFnd As Variant
Dim xStr As String
cFnd = InputBox("Please enter the text, separate them by colon:")
If Len(cFnd) < 1 Then Exit Sub
xArrFnd = Split(cFnd, ":")
For Each Rng In Selection
With Rng
For xFNum = 0 To UBound(xArrFnd)
xStr = xArrFnd(xFNum)
y = Len(xStr)
m = UBound(Split(Rng.Value, xStr))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(Rng.Value, xStr)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & xStr
Next
End If
Next xFNum
End With
Next Rng
Application.ScreenUpdating = True
End Sub
This comment was minimized by the moderator on the site
Thank you for your reply, skyyang!

But it still didn't work.
Perhaps my question was quite confusing. An example may make it clearer.
For example, one of my keywords is "protect". The text in the cells might be displayed as "Our ways to solve the problem: protection and blablabla... " There might be spaces between ":" and "protection" because the formatting of the text is not always the same. Here, the keyword "protection" could not be highlighted by the codes above. That's the problem.

I've tried several times but failed. I have also considered removing the punctuation in the cells, but it would compromise the comprehension of the texts. So I asked the question. Hope you don't mind.

Thank you very much!

Best regards!
This comment was minimized by the moderator on the site
Is there a way to remove all those highlighted words?
This comment was minimized by the moderator on the site
Hi, Ace,
To delete the text based on another column cell, the below vba code may help you, please try it.
Sub DeleteStr()
Dim xStr As String
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xChar As String
Dim i As Long
Dim J As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
LInput:
Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "not support multiple columns"
GoTo LInput
End If
If xRg.Columns.Count <> 2 Then
MsgBox "the selected range can only contain two columns "
GoTo LInput
End If
For i = 0 To xRg.Rows.Count - 1
xStr = xRg.Range("B1").Offset(i, 0).Value
With xRg.Range("A1").Offset(i, 0)
.Font.ColorIndex = 1
For J = 1 To Len(.Text)
If Mid(.Text, J, Len(xStr)) = xStr Then
.Characters(J, Len(xStr)).Delete
End If
Next
End With
Next i
End Sub
This comment was minimized by the moderator on the site
HI
I am regular visitor of your website. Your website is very useful for me. This code changes the font color. I want to highlight particular words (for example Insert, blank) in yellow background. I want to highlight this. Not just a font colour. Keep up the great work!!! Many thanks.
This comment was minimized by the moderator on the site
Hi, Madhusudhanan,
May be there is no direct way for highlighting part of text in a cell with background color in Excel worksheet.
If you have other good method, please comment here.
This comment was minimized by the moderator on the site
i get a run-time error '13', type mismatch when i run the script. any suggestions?
This comment was minimized by the moderator on the site
I had the same issue; I found that one of my collumns were formulas and it was looking in them which was what triggered the error 13. Selected a range wihtout formula containing the text to highlight and it worked.
This comment was minimized by the moderator on the site
Hi,
could anyone help me with the following

my Cells in Column "G" contain the text from Column Z to AN, not compulsory that Column g contains all the text from Z to AN.

My work here is to Highlight the text in Column G if it does not available in any of Column Z - AN

For example : Cell G1 contains (Hello sir I am doing well) but The text "Sir" do not exist in Column "Z1" to "AN1"

So i need to highlight the text "Sir"
This comment was minimized by the moderator on the site
Hi, anyone help me this. i want to highlight the Specific number in Cell within the same sentence. for Ex : " 2 days leave scansion " in this sentence want to highlight number.
This comment was minimized by the moderator on the site
Hi,
Please any one help me. I want to highlight the specific number in same sentence. For ex : " 2 days leave scansion" want to highlight only "2" in sentence.
This comment was minimized by the moderator on the site
This was very useful, thanks very much!
This comment was minimized by the moderator on the site
Awesome. thanks
This comment was minimized by the moderator on the site
Wow! Thank you!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations