How to Highlight Selected Text in Excel (8 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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:

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

Highlight Selected Text in Excel Using Font 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.

Alternative Way to Highlight Selected Text in Excel Using Font Color

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.

Highlight Selected Text Using Cell Styles

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.

Cell selection

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

Highlight Specific Text Using Format Cells

When you are done with all the steps above, you will find your texts highlighted as the picture below:

Highlighted texts in Excel

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.

Highlight Specific Text in Excel Using Conditional Formatting

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.

Cell that Contains Window

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:


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.

New rule in conditional formatting

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
within Format values where this formula is true box.

❸ Pick a formatting color using the Format option.

❹ Finally hit the Ok command.

New formatting rule dialog box

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.

Highlight Text Using Keyboard

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.

VBA Code to Highlight Specific Text

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


Related Articles

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

8 Comments
  1. 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

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

      xTmp = xTmp & Split(Rng.Value, cFnd)(x)
      .Characters(Start:=Len(xTmp) + 1, Length:=y)
      xTmp = xTmp & vbNewLine & 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.

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo