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 a specific text within multiple cells with VBA code
Highlight specific text within a cell based on other text with VBA code
Highlight a 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 screenshot 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:
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.
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:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

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!
