Skip to main content

How to highlight specific text within a cell based on other text?

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 one or more 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 “Sky” in these cells to get the result as following screenshots 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.

VBA code: Highlight a part of text within a cell:

Sub HighlightStrings()
'Updateby Extendoffice
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:

Tips: If you need to highlight multiple keywords from the text strings, please apply the below code:
VBA code: Highlight multiple keywords from text strings:
Sub HighlightStrings()
'Updateby Extendoffice
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
Dim xFNum As Integer
Dim xArrFnd As Variant
Dim xStr As String
cFnd = InputBox("Please enter the text, separate them by comma:")
If Len(cFnd) < 1 Then Exit Sub
xArrFnd = Split(cFnd, ",")
For Each Rng In Selection
With Rng
For xFNum = 0 To UBound(xArrFnd)
xStr = xArrFnd(xFNum)
y = Len(xStr)
m = UBound(Split(Rng.Value, xStr))
If m > 0 Then
xTmp = ""
For x = 0 To m - 1
xTmp = xTmp & Split(Rng.Value, xStr)(x)
.Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
xTmp = xTmp & xStr
Next
End If
Next xFNum
End With
Next Rng
Application.ScreenUpdating = True
End Sub

Then, in the popped out box, please enter the keywords that you want to highlight,(separate the words with a comma), see screenshot:

And then, click OK button,the specified words have been highlighted at once, see screenshots:

Note: The above codes are case sensitive.


Highlight one or more specific text within multiple cells with an amazing feature

If you are not familiar with the code in Excel, here, i will introduce an easy tool - Kutools for Excel, with its Mark Keyword feature, you can highlight the specific one or more keywords at once within the cells.

Note:To apply these Mark Keyword features, firstly, you should download the Kutools for Excel, and then apply the features quickly and easily.

After installing Kutools for Excel, please do as follows:

1. Click Kutools > Text > Mark Keyword, see screenshot:

2. In the Mark Keyword dialog box, please do the following operations:

  • Select the data range that you want to use from the Range textbox;
  • Select the cells contains the keywords that you want to highlight, you can also enter the keywords manually (separate by comma) into the Keyword text box
  • At last, you should specify a font color for highlighting the texts by check Mark keyword colors option. (To color the entire cells containing the keywords, select the Mark the cell content colors option)

3. Then, click Ok button, all specified texts have been highlighted as below screenshot shown:

Note: This feature is not case sensitive, if you want to highlight the text with case sensitive, please check Match Case in the Mark Keyword dialog box.


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 for each row.

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.

VBA code: Highlight a part of text within a cell based on other text:

Sub highlight()
'Updateby Extendoffice
    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:


More relative articles:

  • Bold Part Text When Concatenate Two Columns In Excel
  • In Excel worksheet, after concatenating two cell values with formulas, you may find it will not bold part of the text in the combined formula cell. This may be annoying in sometimes, how could you bold part text when concatenating two columns in Excel?
  • Concatenate Cell Columns And Keep Text Color In Excel
  • As we all known, while concatenating or combining cell columns into one column, the cell formatting (such as text font color, number formatting, etc) will be lost. This article, I will introduce some tricks to combine the cell columns into one and keep the text color as easily as possible in Excel.
  • Display Specific Text Based On Values In Another Column
  • Supposing, I have a list of numbers, now, I want to display some specific text in another column based on this column numbers. For example, if the cell number is between 1-100, I want the text “Decrease” is displayed in adjacent cell, if the number is between 101-200, a text “Stable” is displayed, and if the number larger than 200, a text “Increase” is displayed as following screenshot shown. To solve this task in Excel, the following formulas in this article may help you.
  • Sum Cells With Text And Numbers In Excel
  • For example, I have a list of values containing numerical and textual strings, now, I want to sum only the numbers based on the same text, look at the following screenshot. Normally, you can’t sum the values in the list with text string directly, here, I will introduce you some formulas to deal with this task.

Best Office Productivity Tools

Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

kte tab 201905


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (39)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thank you for "Highlight A Specific Text Within Multiple Cells With VBA Code" It works great. Would you please explain:
I need remove "Highlight"

so what can I do

thanks
This comment was minimized by the moderator on the site
Hello, Mukesh
If you want to delete the specific text from multiple cells, you can apply the Find & Replace feature in Excel.
You just need to enter the specific text that you want to delete into the Find textbox, and leave the Replace box blank, at last, click Replace All to get your results.
Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
This is amazing! One question: Is there any way that an Undo (CTRL+Z) can be used after running this?
This comment was minimized by the moderator on the site
Hello, ChristineW,The vba codes can't support Undo, so when applying the code, you'd better copy and paste the original data to another sheet first.If you use Kutools for Excel, the utility support Undo.
This comment was minimized by the moderator on the site
JUST WANT TO SLAY THANK YOU AS THE VBA FORMULA WORKS FOR ME... IT AWESOME.
This comment was minimized by the moderator on the site
Wow! Thank you!
This comment was minimized by the moderator on the site
Awesome. thanks
This comment was minimized by the moderator on the site
This was very useful, thanks very much!
This comment was minimized by the moderator on the site
Hi,
Please any one help me. I want to highlight the specific number in same sentence. For ex : " 2 days leave scansion" want to highlight only "2" in sentence.
This comment was minimized by the moderator on the site
Hi, anyone help me this. i want to highlight the Specific number in Cell within the same sentence. for Ex : " 2 days leave scansion " in this sentence want to highlight number.
This comment was minimized by the moderator on the site
Hi,
could anyone help me with the following

my Cells in Column "G" contain the text from Column Z to AN, not compulsory that Column g contains all the text from Z to AN.

My work here is to Highlight the text in Column G if it does not available in any of Column Z - AN

For example : Cell G1 contains (Hello sir I am doing well) but The text "Sir" do not exist in Column "Z1" to "AN1"

So i need to highlight the text "Sir"
This comment was minimized by the moderator on the site
i get a run-time error '13', type mismatch when i run the script. any suggestions?
This comment was minimized by the moderator on the site
I had the same issue; I found that one of my collumns were formulas and it was looking in them which was what triggered the error 13. Selected a range wihtout formula containing the text to highlight and it worked.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations