How to Find and Replace Text Color in Excel (3 Quick Ways)

Certainly, finding and replacing text color in Excel is a common task that we have to perform in order to organize, highlight, and present data in a clear and meaningful way. In this article, you are going to learn 3 ways to find and replace text color in Excel according to your needs. Moreover, you’ll also explore changing text color with formula and font color based on value.

The GIF below is an overview of this article which shows how to find and replace text color.

Overview of Finding and Replacing Text Color in Excel

In the following sections, you’ll learn more about the dataset and observe each method in detail.

How to Find and Replace Text Color in Excel: 3 Ways

Now, let’s consider the Net Payable Salaries of Ten Employees dataset shown in the B4:G14 cells which contains the Name, Designation, Basic Salary, Medical Expenses, Bonus, and Total columns respectively. Henceforth, without further delay, let’s see each method in action.

Dataset showing the Name, Designation, Basic Salary, Medical Expenses, Bonus, and Total columns.

1. Using Find and Replace Feature to Change Text Color

This is the fastest method to find a particular Text and change its font color where we’ll apply the Find and Replace feature of Excel. Let’s look at the steps below:

📌 Steps:

  • Firstly, select the range of cells from where you need to find the Text.
  • Then, Click on Find & Select under the Home tab.
  • From the drop-down menu, click on Replace

Selecting Replace Option of Find & Select Feature in the Home ribbon

  • A dialog box named Find and Replace will appear. Under the Replace tab, Click on Options.

Selecting Options to Format in the Find and Replace wizard

  • In the Find what: box, write down the text you want to find and click on Format… beside the Replace with: box.

Writing the Targeted Text and Selecting Replacing Criteria

  • A window named Replace Format will appear. Click on the Font tab.
  • Click-on Color. Then, select a color according to your preference.

Choosing the Font Color of the Text 

  • Press OK.

Finalizing Formatting Task in the Replace Format Window

  • Then, inside the Find & Select box, press Replace All.

Pressing Replace all Command to Change the Font Colors of the Found Words

  • Finally, all the targeted text colors will be changed.

Results Showing Changed Text Colors after Applying Find and Replace Feature

2. Find and Replace Text Color Manually

Finding and Replacing text color manually is one of the simplest tasks you can perform on MS Excel. For one thing, it can also become very tiring and highly time-consuming if the dataset you are working with contains huge amount of data but if we have a small set of data, then this can be a quick fix.

📌 Steps:

  • First, select all the texts you want to change the colors. (By pressing CTRL on the keyboard, you can select the cells consequently)
  • Next, you click on the Font Color icon under the Home tab and choose any of the default or custom colors.
  • Now, the selected texts have different colors.

GIF Showing the Steps of Changing Text Color Manually

3. Applying VBA Code to Find and Replace Text Color

Now, If you want to change the color of a particular text, but not the colors of the other texts, that share the same cell with the previous one, a VBA macro can be your only friend.

📌 Steps:

  • In the beginning, click on Visual Basic under the Developer tab.

As a note, if you’re having trouble finding the Developer tab, then follow this article to go through the steps.

Opening Visual Basic in the Developer Tab

  • A new window named Microsoft Visual Basics for Applications will open.
  • Click on Insert and select Module from the drop-down menu.

Inserting Module to Start Writing the Code.

  • Inside the module write down your macro to change the color of a specific text, every time it appears anywhere in the sheet or in the entire workbook.

VBA Code to Find and Replace Text Color in Excel

Sub change_text_color()

input_word = InputBox("What word to change")
txt_len = Len(input_word)

For Each cell In Selection
    For x = 1 To Len(cell.Value)
        On Error Resume Next
        wrd = 0
        wrd = Application.WorksheetFunction.Find(input_word, cell.Value, x)
        On Error GoTo 0
        If wrd > 0 Then
            cell.Characters(Start:=wrd, Length:=txt_len).Font.Color = RGB(255, 0, 255)
            x = wrd + 1
            Exit For
        End If
    Next x
Next cell
End Sub

Code Breakdown:

In this section, we’ll explain the VBA code used to find and replace text color in Excel.

  • First, name the subroutine, here it is change_text_color().
  • Next, prompt the user for a target word and store it in the input_word variable.
  • Then, apply the Len function to determine the number of characters.
  • Second, nest multiple For Next loops to go through all the cells in the selected range, using the Find method to locate them.
  • Lastly, apply an If Then statement to apply color to the matched words.
  • Afterward, go back to your Excel sheet and click on Macros under the Developer tab.
  • Select the macro, in this case, change_text_color and click on Run.

Executing VBA Macro

  • A prompt will appear asking you, “What word to change?” Type the word and press OK.

Typing the Desired Text and clicking ok.

  • The color of the word will change in every cell, without changing the text colors of the other words inside the same cells.

Results of Find and Replace Text Color in ExcelUsing VBA Code

Read More: How to Find and Replace within Selection in Excel

How to Change Text Color in Excel Using Formula

On a different note, other than Find and Replace, we can also change text color using Excel formula, so just follow the steps below:

📌 Steps:

  • First, choose the range from where you need to find the text.
  • Then, press Conditional Formatting in the Styles scetion.
  • From the drop-down menu, place the cursor on Highlight Cells Rules and select a condition according to your need.
  • A dialog box will open. There, type your conditional argument.
  • Press OK. The text colors will be changed accordingly.

GIF Showing the Process of Changing Text Color Using Formula in Conditional Formatting

Read More: How to Find and Replace Using Formula in Excel

How to Change Font Color Based on Value in Excel

Similarly, font colors may also be changed depending on specific value conditions set by you. If you like the idea, just follow the steps below:

📌 Steps:

  • As before, select the cells.
  • Choose Conditional Formatting and select the New Rule option.
  • Afterward, this opens the New Formatting Rule wizard. Select Use a formula to determine which cells to format.
  • Inside the Format values where this formula is true: box, type your conditional formula. An example could be:

which would mark the even numbers residing in column G.

  • Click on Format… Then, choose any color under the Font tab.
  • Press OK (Format Cells box). Again press OK (New Formatting Rule box).
  • The colors of the texts that match the conditional formula value, will be changed accordingly.

GIF Showing the Steps of Changing Font Color Based on Value with  Conditional Formatting

In truth, we’ve skipped some of the relevant examples of how to change font color based on value, which you may explore.

Practice Section

We have provided a Practice section on the last worksheet of the provided workbook so you can practice yourself. Please make sure to do it by yourself.

Practice Section for find and replace text color in excel

Download Practice Workbook

You can download and practice the dataset we used to prepare this article.


To sum up, you understand how easy it is to find and replace text color in Excel using the above-mentioned methods (whichever meets your need efficiently). Now, use the methods whenever you need and let us know if you have any better or additional way to carry out the task. Moreover, you can reach us via the comment section below for any queries.

Related Articles

<< Go Back to Find and Replace | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF