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 need. Moreover, you’ll also explore changing text color with formula and changing font color based on value.
The GIF below is an overview of this article which shows how to find and replace text color.
In the following sections, you’ll learn more about the dataset and observe each method in detail.
Download Practice Workbook
You can download and practice the dataset we used to prepare this article.
3 Ways to Find and Replace Text Color in Excel
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.
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…
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 on the Font tab.
Click-on Color. Then, select a color according to your preference.
Press OK.
Then, inside the Find & Select box, press Replace All.
Finally, all the targeted text colors will be changed.
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.
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.
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 in 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
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.
A prompt will appear asking you, “What word to change?” Type the word and press OK.
The color of the word 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
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.
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:
=ISEVEN(G5)
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.
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.
Conclusion
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.
The post How to Find and Replace Text Color in Excel (3 Quick Ways) appeared first on ExcelDemy.
