How to Change Text Color with Formula in Excel (2 Methods)

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.

Steps for Greater Than option:

Now you will see that here it’s showing the outputs with our picked color.

Steps for Greater Than option:

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.

Steps for Less Than option:

Now take a look that it has changed the color of the text with our selected color.

Steps for Less Than option:

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.

Steps for Between option:

Here’s our output-

Steps for Between option:

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.

Steps for Equal To option:

Now you will observe that the text color is changed.

Steps for Equal To option:

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

New Rule

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.

New Rule

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.

New Rule

Step 4:

⏩ At this moment just press OK again.

New Rule

Here’s our output with picked text color

Read More: Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)


Similar Readings:


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.

VBA Macros to Change Text Color in Excel

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.

VBA Macros to Change Text Color in Excel

Step 3:

⏩ Give a macro name.

⏩ Later, press Create.

VBA Macros to Change Text Color in Excel

Step 4:

⏩ Then press the Run icon again to run the codes.

VBA Macros to Change Text Color in Excel

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

VBA Macros to Change Text Color in Excel

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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo