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

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

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

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.

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: