How to Find and Replace Text Color in Excel: 3 Quick Methods

Method 1 – Using Find and Replace Feature to Change Text Color

Steps:

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

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

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

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

• Press OK.

• Inside the Find & Select box, press Replace All.

• All the targeted text colors will be changed.

Method 2 – Find and Replace Text Color Manually

Steps:

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

Method 3 – Applying VBA Code to Find and Replace Text Color

Steps:

• Click on Visual Basic under the Developer tab.

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

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

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

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
        Else
            Exit For
        End If
    Next x
Next cell
End Sub

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

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

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

How to Change Text Color in Excel Using Formula

Steps:

• Choose the range from where you need to find the text.
• Press Conditional Formatting in the Styles section.
• 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.

How to Change Font Color Based on Value in Excel

Steps:

• Select the cells.
• Choose Conditional Formatting and select the New Rule option.
• 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. Choose any color under the Font tab.
• Press OK (Format Cells box). Press OK (New Formatting Rule box).
• The colors of the texts that match the conditional formula value, will be changed accordingly.

We skipped some relevant examples of how to change font color based on value, which you may explore.

Download Practice Workbook

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

Related Articles

• How to Show Dash Instead of Zero in Excel
• How to Find and Replace Multiple Words from a List in Excel
  
• How to Replace Text in Excel Formula
• How to Find and Replace Values in Multiple Excel Files
• Replace Text of a Cell Based on Condition in Excel

<< Go Back to Find and Replace | Learn Excel