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