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 specific text. Tried several functions (vlookup, xlookup, hlookup, match, xmatch, conditional fomat etc) but unfortunately without the succes. Can somebody help me with this?

 

many thanks in advance

 

  • 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

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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

    • gijsvancuijk's avatar
      gijsvancuijk
      Copper Contributor

      NikolinoDE 

       

      Hello NikolinoDE,

       

      many many thanks for your VBA script, it works perfectly. One small remark/question: the search is case sensitive and i want a case insensitive search. Of course i tried to fix it by myself but unfortunately without succes. It should be GREAT if you have a solution for this.

       

      Already again many thanks for the script.

       

      regards gijs

       

         

       

       

Resources