Forum Discussion
gijsvancuijk
Dec 25, 2022Copper Contributor
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...
- Dec 25, 2022
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.
- 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.
- 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
- Then press F5 key to run this code, a prompt box will appear to remind you to enter the text.
- 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.
merry Xmas
NikolinoDE
Dec 28, 2022Gold Contributor
Add in VBA
Option Explicit
Option compareText
then the VBA code
Here is an example file to ignore uppercase or lowercase.
gijsvancuijk
Dec 29, 2022Copper Contributor
perfect, thankssssssssssssss