Mrinmoy Roy
To highlight text is one of the most used and basic techniques that we need to perform in Excel. It is super easy to highlight texts within a moment. Besides, there are multiple ways available to do this. To facilitate you with all the ways, we have come up with 8 techniques throughout this article that you can use to highlight text with ease in Excel.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
8 Ways to Highlight Selected Text in Excel
In this article, we will be using a sample product price list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:
So, without having any further discussion let’s dive straight into all the methods one by one.
1. Highlight Selected Text in Excel Using Font Color
There’s a dedicated tool for text highlighting under the Home ribbon. It’s easily accessible as well as very convenient to use at the same time. To use this tool to highlight your texts,
❶ Select the range of cells ▶ that you want to highlight.
❷ Then go to the Home ribbon.
❸ Now navigate to the Font group.
Within this group, hit the Font Color icon to highlight your selected text with color.
You can use the same feature of Excel using another way. This technique requires even less time and is easy to use. All you need to do is,
❶ Select the range of the cells.
❷ Right-click on your mouse.
This will bring a pop-up list on spot. On the top of the list, you will easily see the Font Color icon.
❸ Just hit the Font Color icon.
That’s it.
Read more: How to Highlight Cells Based on Text in Excel [2 Methods]
2. Highlight Selected Text Using Cell Styles
You can use another feature inside Excel called Cell Styles. This feature will offer you to highlight your cells as well as your texts within a moment. All you need to do is,
❶ Select the range of cells ▶ to highlight the texts within them.
❸ Then go Home ▶ Cells Styles.
After hitting the Cell Styles command, you will see a bunch of formatting options to highlight cells as well as texts within them. From the list,
❹ Click on Warning Text.
This will highlight your text with red color.
Read More: How to Highlight a Cell in Excel (5 Methods)
3. Highlight Specific Text Using Format Cells
Format Cells is an amazing feature inside Excel that enables us to format everything that we need inside Excel worksheets. It’s also super easy to use. All you need to do is,
❶ Select the range of cells ▶ where you want to apply the text formatting.
❷ After the press CTRL + 1 to open up the format cells dialog box.
❸ Select the Font ribbon within the dialog box.
❹ Now choose a color within the Color box.
❺ Finally hit the Ok option.
When you are done with all the steps above, you will find your texts highlighted as the picture below:
Read More: How to Highlight Selected Cells in Excel (5 Easy Ways)
4. Highlight Specific Text in Excel Using Conditional Formatting
Maybe one can get the most flexibility about text or cell whatever formatting is required using the Conditional Formatting feature in Excel. Now in this section, we will see how we can format cells that contain specific text using Conditional Formatting.
❶ First of all select the whole data table.
❷ Then go to Home ▶ Conditional Formatting ▶ Highlight Cells Rules ▶ Text that contains.
After hitting the Text that Contains command, you will see a dialog box pops up on the screen. Within the box,
❶ Type texts based on which you want to format cells.
For instance, we’ve typed OP. This will highlight all the cells that contain the text OP in them.
❷ After that hit the Ok command.
When you are done with all the steps above, you will see your texts are highlighted as the picture below:
Read More: Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)
Similar Readings:
- Excel Formula Based on Cell Color (5 Examples)
- How to Fill Color in Excel Cell Using Formula (5 Easy Ways)
- How to Fill Cell with Color Based on Percentage in Excel (6 Methods)
5. Highlight Text in Excel Using Formula
Now we will highlight specific text with the help of a formula. We will set criteria using an Excel formula that will highlight all the texts that meet up the set criteria.
Suppose we want to highlight all the records that contain a quantity of more than 25. Now follow the steps below to see how to do this trick.
❶ First of all select the range of cells.
❷ Go to Home ▶ Conditional Formatting ▶ New Rule.
After hitting on the New Rule command, the New Formatting Rule dialog box will appear. Within the box,
❶ Select Use a formula to determine which cells to format.
❷ Then enter the formula: =$C5>25
❸ Pick a formatting color using the Format option.
❹ Finally hit the Ok command.
As soon as you are done with all the steps above, you will see your intended records have been highlighted as the picture below:
Read More: Excel VBA to Highlight Cell Based on Value (5 Examples)
6. Highlight Text Using Keyboard
You can use your keyboard only to highlight texts with ease. All you need to do is,
❶ Select a cell first.
❷ Then hold down the SHIFT and press any of the Arrow Keys to highlight cells.
Read More: How to Change Cell Color Based on a Value in Excel (5 Ways)
7. Highlight Text Using Mouse
You can highlight text more easily with a mouse than with the keyboard. All you need to do is,
❶ Select a cell.
❷ Left-click the mouse and drag it to highlight your intended cells.
Related Content: How to Highlight from Top to Bottom in Excel (5 Methods)
8. Highlight Specific Text Using VBA Code
For example, you have a large dataset with tons of text and among them, you want to highlight a specific text. If so, you can use the following VBA code to highlight your intended text with ease.
For instance, we want to highlight NPP throughout the dataset. To do so,
❶ Press ALT + F11 to open up the VBA editor.
❷ Go to Insert ▶ Module.
❸ Copy the following VBA code:
Sub TextHighlighter()
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)
Color_Code = Int(InputBox("Enter the Color Code: " + vbNewLine + "Enter 3 for Color Red." + vbNewLine + "Enter 5 for Color Blue." + vbNewLine + "Enter 6 for Color Yellow." + vbNewLine + "Enter 10 for Color Green."))
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 = Color_Code
xTmp = xTmp & cFnd
Next
End If
End With
Next Rng
Application.ScreenUpdating = True
End Sub❹ Now paste and save the code in the VBA editor.
❺ After that go back to the Excel workbook and select the whole data table.
❻ Then press ALT + F8 keys together.
This will open up the Macro window.
❼ From the window select the function TextHighlighter and hit the Run command.
After this, an input box will appear. Within the box,
❽ Insert NPP to highlight the NPP text throughout the table and hit Ok.
❾ Then insert a color code as instructed. For instance, we’ve inserted 3 to pick the red color.
❿ Finally, hit the Ok button.
When you are done with all the steps above, you will see your intended text that is NPP is highlighted with the red color as in the image below:
Read More: VBA to Change Cell Color Based on Value in Excel (3 Easy Examples)
Things to Remember
📌 Press CTRL + 1 to open the Format Cells dialog box.
📌 You can press ALT + F8 to open the Macro window.
📌 Press the ALT + F11 keys together to open up the VBA editor.
Highlight Text in Excel: Knowledge Hub
Conclusion
To sum up, we have discussed 8 methods to highlight text in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
Thank you for this. I was wondering if the code could be written so that it would pick up a certain number of characters after a text of string.
For example, I want it to hilight the text “Multiplier” as well as the following 7 characters. Is that possible?
Hi Scot,
The following code may fulfill your requirements.
Sub TextHighlighter()
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, ext As Long
cFnd = InputBox(“Enter the text string to highlight”)
Color_Code = Int(InputBox(“Enter the Color Code: ” + vbNewLine + “Enter 3 for Color Red.” + vbNewLine + “Enter 5 for Color Blue.” + vbNewLine + “Enter 6 for Color Yellow.” + vbNewLine + “Enter 10 for Color Green.”))
ext = CLng(InputBox(“Input number of additional Character to color”, , 0))
y = Len(cFnd) + ext
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 = Color_Code
xTmp = xTmp & cFnd
Next
End If
End With
Next Rng
Application.ScreenUpdating = True
End Sub
Hi!
Could you help me?… I´m trying to introduce a breakline in the string before cFnd.
This is the code:
xTmp = xTmp & Split(rng.Value, cFnd)(x)
.Characters(Len(xTmp) + 1, 0).Insert vbNewLine
But my problem is that I get an 1004 error with insert. Could you gave me another alternative code?
Hello, GABRIEL!
Thanks for sharing your problem with us!
To add a new line you don’t have to write the command .Insert. You can simply use this block of the code to breakline in the string before cFnd.
To use vbNewLine, you have to make sure to do the following.
1. After the ampersand (&) symbol, press the spacebar and get the VBA constant ‘vbNewLine‘.
2. After the constant ‘vbNewLine‘, press one more time space bar and add the ampersand (&) symbol.
3. After the second ampersand (&) symbol, type one more space character, and add the next line sentence in double-quotes.
In VBA, there are three different (constants) to add a line break.
vbNewLine, vbCrLf, vbLf
If this is not working for you, follow the steps.
1. Click on the character you wish to break the line from first.
2. Then, enter a space ( ).
3. Type an underscore (_) after that.
4. Finally, press Enter to finish the line.
Hope this will help you!
Good Luck!
Regards,
Sabrina Ayon
Author, ExcelDemy.
Hello, Thanks For 8 methods to highlight text in Excel. i Have a Problem with last method.
The code works in (English characters) But when i input for example Persian character such as (توسعه), do not Work!
Thank you MOhamad for your query. The Persian language is not available in the UNICODE. That’s why you are facing the problem. If you want to use the code for Persian characters, you have to modify the code slightly. The code can not take Persian language in the InputBox. So, instead of InputBox, we have to insert text to be highlighted in a cell. Let’s insert Persian text to be highlighted in the C10 cell. So, the modified vba code should be like this one.
Sub Text_Highlighter()
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 = Range(“C10”).Value
y = Len(cFnd)
Color_Code = Int(InputBox(“Enter the Color Code: ” + vbNewLine + “Enter 3 for Color Red.” + vbNewLine + “Enter 5 for Color Blue.” + vbNewLine + “Enter 6 for Color Yellow.” + vbNewLine + “Enter 10 for Color Green.”))
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 = Color_Code
xTmp = xTmp & cFnd
Next
End If
End With
Next Rng
Application.ScreenUpdating = True
End Sub
You should insert your preferred cell instead of C10. And this code will highlight characters of all other languages.
Thank you for your guidance. The solution you gave worked.
Dear Mohamad,
Thanks for your appreciation
Regards
ExcelDemy