How to highlight specific text in Excel

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 a 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 “sum” in these cells to get the result as following screenshot 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.

Public 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
    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:

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.

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.

Sub Highlight()
    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:

Download the example for the download section. This file contains macros.

Enjoy Excel!