Forum Discussion

gijsvancuijk's avatar
gijsvancuijk
Copper Contributor
Dec 25, 2022

Excel specific text search in one cell

Hello Excel Profs,   i am searching for a way how to search in an excel sheet for a specific text AND mark/highlight this part. With the normal search function the cell is selected but not the spec...
  • NikolinoDE's avatar
    Dec 25, 2022

    gijsvancuijk 

    Highlight one or more specific texts in multiple cells with VBA code

    For example, I have a series of text strings and now I want to highlight the specific text.

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

     

    1. Select the cells where you want to highlight the specific text, and hold the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
    2. Click Insert > Module, and paste the following code into the Module window.

     

    VBA code: Select a part of text in a cell:

     

     

    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

     

     

    1. Then press F5 key to run this code, a prompt box will appear to remind you to enter the text.
    2. And then click OK button, all the text you specified has only been highlighted in the cells.

    Hope I could help you with this information and link.

     

    NikolinoDE

    merry Xmas

Resources