Md. Sourov Hossain Mithun
Changing text color is a very important task in Excel. It helps in many ways to highlight and analyze data. We can do it manually but there is no built-in function or formula to change text color. We can use a formula in Conditional Formatting or make a user-defined function by using VBA macro to do the task. In this article, I have explained two methods with easy steps to change text color with formula in Excel.
Download Practice Book
You can download the free Excel template from here and practice on your own.
2 Methods to Change Text Color with Formula in Excel
Let’s get introduced to our dataset first. I have placed some books’ names and their online prices for two consecutive years. We’ll change the color of the prices with the formula.
Method 1: Formula with Conditional Formatting to Change Text Color in Excel
Conditional Formatting helps to format cells with highlighted colors, icons based on our specified conditions and which is very useful to highlight data too easily. We’ll use the Highlight Cells Rules and New Rule options to change text color in Excel.
#1 Highlight Cells Rules
There are some built-in commands in the Highlight Cells Rules option which is like a formula. We’ll use that to change text color.
Steps:
⏩ Select the data range C5:D12
⏩ Then click as follows: Home > Conditional Formatting > Highlight Cells Rules
⏩ Later, you will get 4 options which are- Greater Than/ Less Than/ Between/ Equal To.
Select which one you need.
Steps for Greater Than option:
At first, I’ll show the application of the Greater Than option.
⏩ Select your desired range in Format cells that are GREATER THAN box.
I have set the greater than value $120. That means it will change the color of the text and highlight only those values which are greater than $120.
⏩ Then select your desired color.
⏩ Finally, just press OK.
Now you will see that here it’s showing the outputs with our picked color.
Steps for Less Than option:
Now we’ll change the color of the text which is less than a selected value.
⏩ Type the less than the value in Format cells that are LESS THAN box.
I have set the less than the value at $110. That means it will change the color of the text and highlight only those values which are less than $110.
⏩ Later, select your desired color.
⏩ Then just press OK.
Now take a look that it has changed the color of the text with our selected color.
Steps for Between option:
Lets’s change the color of the text which is between two selected values.
⏩ Set the start and end range values in Format cells that are BETWEEN box.
I have set $105 and $135. So it will change the color of the text and highlight only those values which are between $105 to $135.
⏩ Then select your desired color.
⏩ Later, just press OK.
Here’s our output-
Steps for Equal To option:
Now we’ll change the text color of those values which are equal to a specific value.
⏩ Set the value in Format cells that are EQUAL TO box.
I have set $150.
⏩ Then select your desired color.
⏩ Finally, just press OK.
Now you will observe that the text color is changed.
#2 New Rule
In the New Rule option of Conditional Formatting, you can use any of your desired formulas to change text color. Here, I’ll change the text color of that rows if the values of Column C are greater than Column D.
Step 1:
⏩ Select the data range C5:D12
⏩ Click as follows: Home > Conditional Formatting > New Rule
A dialog box will appear then.
Step 2:
⏩ Press Use a formula to determine which cells to format from the Select a Rule Type dialog box.
⏩ Then type the formula given below in Format values where this formula is true box.
⏩ Later, press Format.
Another dialog box will open up.
Step 3:
⏩ Now set your desired color from the Color box of Font option. I have picked orange color.
⏩ Press OK and it will go back to the previous dialog box.
Step 4:
⏩ At this moment just press OK again.
Here’s our output with picked text color
Read More: Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)
Similar Readings:
- Apply Conditional Formatting to Each Row Individually: 3 Tips
- Conditional Formatting Entire Column Based on Another Column(6 Steps)
- Conditional Formatting on Text that Contains Multiple Words in Excel
- How to Do Conditional Formatting with Multiple Criteria (11 Ways)
- Pivot Table Conditional Formatting Based on Another Column (8 Easy Ways)
Method 2: Use of VBA Macros to Change Text Color in Excel
We can make our user-defined function with Excel VBA to change text color. I have named the function TextColor. Let’s see how to make it with codes.
Step 1:
⏩ Right-click your mouse to the sheet title.
⏩ Select View Code from the context menu.
A VBA window will open up.
Step 2:
⏩ Write the codes given below-
Function TextColor(n1 As Double, n2 As Double) As Boolean
If n1 <= n2 Then
Application.Caller.Font.ColorIndex = 3
Else
' Set font to normal
Application.Caller.Font.ColorIndex = xlAutomatic
End If
End Function
Sub Tcolor()
End Sub⏩ Then press the Run icon a macro dialog box will appear.
Step 3:
⏩ Give a macro name.
⏩ Later, press Create.
Step 4:
⏩ Then press the Run icon again to run the codes.
Now our function is ready to use. If the condition C5>D5 meets then it will show 1 with normal color if not then it will show 0 with red color.
Step 5:
⏩ Type the given formula in Cell D14-
=IF(C5>D5,1,0)+TextColor(C5,D5)⏩ Hit the Enter button
Now see that the condition doesn’t meet so it has return 0 with red color.
Read More: Excel Formula to Change Text Color Based on Value (+ Bonus Methods)
Conclusion
I hope all of the methods described above will be good enough to change text color in Excel with formula. Feel free to ask any question in the comment section and please give me feedback.
Related Articles
- How to Change a Row Color Based on a Text Value in a Cell in Excel
- Change the color of text and background of cells – An Excel Font and Fill Color Complete Guide
- 3 Methods to Convert Scientific Notation to Text in Excel
- Excel VBA to Highlight Specific Text in a Cell (6 Ways)
- Conditional Formatting Text Color (3 Easy Ways)
