Dec 24 2022 06:46 PM
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
Dec 24 2022 11:07 PM
SolutionHighlight 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.
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
Hope I could help you with this information and link.
merry Xmas
Dec 27 2022 09:51 PM
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
Dec 28 2022 01:44 AM
Add in VBA
Option Explicit
Option compareText
then the VBA code
Here is an example file to ignore uppercase or lowercase.
Dec 24 2022 11:07 PM
SolutionHighlight 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.
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
Hope I could help you with this information and link.
merry Xmas