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:
|
|
|
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.
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
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...
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!
