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

Get FREE Advanced Excel Exercises with Solutions!

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 a VBA macro to do the task. In this article, I have explained two methods with easy steps to change text color with a formula in Excel.


Excel Formula to Change Text Color: 2 Easy Methods

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, and icons based on our specified conditions which is very useful for highlighting data easily. We’ll use the Highlight Cells Rules and New Rule options to change text color in Excel.

1.1 Use 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 that 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 that 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:

Let’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 that 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 that 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 has changed.

Steps for Equal To option:


1.2 Apply 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 those 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 the 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 the picked text color.


Method 2: Using 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 on 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 returned 0 with red color.


Download Practice Book

You can download the free Excel template from here and practice on your own.


Conclusion

I hope all of the methods described above will be good enough to change text color in Excel with the formula. Feel free to ask any question in the comment section and please give me feedback.


Related Articles


<< Go Back to Color Cell in ExcelExcel Cell FormatLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo