How to Find & Replace texts in Excel with a formatted color

Question

I'm new to Excel. I am trying to find and replace text with a different font color. Also, I cannot seem to change simply one word. The entire cell's content change to the new font color. Is this possible to do?

Ex:

Ravishing Ruby .. Mid Am '22 .. Johnson '16.

I want to find every occurrence of the word Johnson in a column of 353 entries. I want to change the text color from black to royal blue. I do not want the entirety of the cell to change to royal blue, just the name Johnson.

---

Moved from: Community Center | Discuss the Q&A site | Get started on Q&A

Answer 1

In Excel, the built-in Find and Replace feature does not support changing the font color of only specific words within a cell. When you use Find and Replace, it applies formatting changes to the entire cell content rather than individual words.

However, you can achieve your goal by using a combination of Find and Replace along with some manual formatting. Here’s how you can do it:

1. Find the Text: Use the Find feature (Ctrl + F) to locate the occurrences of the word "Johnson" in your column.
2. Select Each Instance: For each occurrence found, manually select the word "Johnson" within the cell.
3. Change Font Color: After selecting the word, go to the Home tab, and in the Font group, choose the font color you want (e.g., royal blue).

This method allows you to change the color of just the word "Johnson" without affecting the rest of the text in the cell.

If you have many occurrences and want to automate the process, you might consider using a VBA macro to loop through the cells and change the font color of specific words. Here’s a simple example of how that might look:

Sub ChangeColorOfWord()
    Dim cell As Range
    Dim wordToFind As String
    Dim startPos As Long
    Dim lengthOfWord As Long

    wordToFind = "Johnson"
    lengthOfWord = Len(wordToFind)

    For Each cell In Selection
        startPos = InStr(cell.Value, wordToFind)
        If startPos > 0 Then
            With cell.Characters(startPos, lengthOfWord).Font
                .Color = RGB(65, 105, 225) ' Royal Blue
            End With
        End If
    Next cell
End Sub

You can run this macro after selecting the range of cells you want to modify. This will change the font color of the word "Johnson" to royal blue without changing the rest of the cell's content.

---

References:

• Programmatically search for Text in worksheet ranges