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

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

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.

Here’s our output-

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.

Now you will observe that the text color has changed.

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.

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

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–

⏩ Hit the Enter button.

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

• Uses of CELL Color A1 in Excel
• How to Get Cell Color in Excel
• How to Color Code Cells in Excel
• Excel Formula to Change Cell Color Based on Text
• How to Change Cell Color Based on a Value in Excel
• How to Fill Color in Cell Using Formula in Excel
• How to Fill Cell with Color Based on Percentage in Excel
• Excel Formula to Color Cell If It Has Specific Value
• How to Apply Formula Based on Cell Color in Excel
• VBA to Change Cell Color Based on Value in Excel