Excel Formula to Change Text Color Based on Value (+ Bonus Methods)

Datastore, process, and manipulation are very familiar uses of Microsoft Excel. When we work with a large amount of data, sometimes we need to identify our required data by changing the color of the data. In this article, we will discuss how to change text color based on the value with Excel Formula with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Excel Formulas to Change Text Color Based on Value

We will demonstrate several methods to change the text color based on the value in Excel with formulas.

For this purpose, we have taken a data set of some with the balance of their bank account.

Data set for Excel Formulas to Change Text Color Based on Value

No native Excel formula alone can change the text color. The Excel Conditional Formatting and Excel VBA Macros are used to change the text color based on conditions. In the following section, we will provide 3 formulas to use in Excel Conditional Formatting and change the text color based on value.

Note:

Conditional Formatting can work only with the functions that return only TRUE or FALSE. Example: OR, ISODD, ISNUMBER, AND, COUNTIF, etc. functions.


Formula 1: Change Text Color Based on Value with ISODD in Conditional Formatting

The ISODD function finds an odd number. In this method, we will use this function-based Excel formula to change the text color based on value.

Step 1:

  • First, go to the Home tab.
  • Select the New Rule from the Conditional Formatting.

Change Text Color Based on Value with ISODD in Conditional FormattingStep 2:

  • Now, select Use a formula to determine which cells to format as the Rule Type.
  • Write the formula as mentioned on the image. The formula is:
=ISODD(C5)
  • Then click on the Format.

Change Text Color Based on Value with ISODD in Conditional FormattingStep 3:

  • Now, select the font color and press OK.

Step 4:

  • We will get a Preview of the font color.
  • Then, press OK.

Step 5:

  • Now, select Manage Rules from the Conditional Formatting.

Change Text Color Based on Value with ISODD in Conditional Formatting

Step 6:

  • First, select This Worksheet to show the applied rules on the present workbook.
  • Then select the applied area.

Step 7:

  • Finally, press OK.

We can see that the odd numbers on the Balance column are showing with changing color.

Read More: Excel Conditional Formatting Text Color (3 Easy Ways)


Formula 2: Change Text Color Based on Value with OR in Conditional Formatting

We will use the OR function here. Let’s see the following steps.

Step 1:

  • We go to the New Rule option as shown previously.
  • Now, write the formula:
=OR(B5="John",C5>400)
  • Also, select the format like previously.
  • Then press OK.

Change Text Color Based on Value with OR in Conditional Formatting

Step 2:

  • Now, go to manage the rules shown previously to specify the area.

Change Text Color Based on Value with OR in Conditional Formatting

Step 3:

  • Then press OK.

We see that the text color is changed.

We can also use the AND function in the same way.

Read More: Change Font Color Based on Value of Another Cell in Excel (2 Methods)


Similar Readings:


Formula 3: Change Text Color Based on Value with COUNTIF in Conditional Formatting

The COUNTIF function is used to count something with given criteria.

Step 1:

  • We modify the data set for applying the mentioned formula.

Step 2:

  • Now, write the formula in the New Rule option shown previously.
  • The formula will be:
=COUNTIF(C5,B5)>0

Change Text Color Based on Value with COUNTIF in Conditional Formatting

Step 3:

  • Then go to the manage rule to set the area.

Change Text Color Based on Value with COUNTIF in Conditional Formatting

Step 4:

  • Then press OK.

We see that the matching names of column B are turned into the red as we mentioned.

Read More: How to Change a Row Color Based on a Text Value in a Cell in Excel


More Methods to Change the Text Color Based on Value in Excel

In truth, using self-made Excel formulas in Conditional Formatting is not much flexible to work with. So, we will show some other handy methods to change the text color based on value.


1. Change Text Color Based on Value by Applying Built-in Conditional Formatting

In this section, we will change the text color based on value using Conditional Formatting.


Condition 1: Negative/Positive Value

Let’s see the steps below.

Step 1:

  • Select all the cells of the Balance column.

 

Step 2:

  • Now, go to the Home tab.
  • Select Conditional Formatting from the commands.
  • Choose New Rule.

Change Text Color Based on Value by Applying Built-in Conditional Formatting

Step 3:

  • After clicking New Rule, we get a new window.
  • Select Format only cells that contain the Rule Type.
  • Chose Cell Value.
  • Here, we will change color based on negative values.
  • Select less than and 0 the mentioned box of the images.

Change Text Color Based on Value by Applying Built-in Conditional Formatting

Step 4:

  • Now, click on Format.
  • Select Font color from the new window.
  • Then press OK.

Step 5:

  • We will see a Preview of the selected font color.

Step 6:

  • Finally, press OK.

Here, all the negative values are colored as selected.

Step 7:

  • In the same way, we can also change the text color of the positive values.

Change Text Color Based on Value by Applying Built-in Conditional Formatting

Step 8:

  • Press OK again.

We see that positive and negative values are colored differently.

Read More: How to Make Negative Numbers Red in Excel (3 Ways)


Condition 2: Greater/Less than

Here, we will change the text color by comparing greater or less than any value.

Greater than a Value:

Step 1:

  • Select the cells first.
  • Go to the Home tab.
  • Select Conditional Formatting from the commands.
  • From the Highlight Cells Rules, choose Greater Than.

Change Text Color Based on Value by Applying Built-in Conditional Formatting

Step 2:

  • In the Greater Than window, enter a value to see which values are greater than this value.
  • Now, select Custom Format to select the font color.

Change Text Color Based on Value by Applying Built-in Conditional Formatting

Step 3:

  • Select our desired font color.

Step 4:

  • Then press OK.

Step 5:

  • Again, press OK.

Now, we can see that font colors are changed to get a value greater than we set.

Less than a Value:

We can also change color by comparing less than our desired value. See the steps below.

Step 1:

  • Select Less Than from the Highlight Cells Rules.

Change Text Color Based on Value by Applying Built-in Conditional FormattingStep 2:

  • We set a value in the Less Than window.
  • Also, set a Custom Format.

Change Text Color Based on Value by Applying Built-in Conditional FormattingStep 3:

  • Now, press OK.

Change Text Color Based on Value by Applying Built-in Conditional FormattingHere, we see that both greater or less than value font colors are changed as we set them.

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


Condition 3: If Contains Specific Text

We will look for specific text here and change the color of that text.

Step 1:

  • Select the Name column.

Step 2:

  • Go to the Home tab.
  • Select the Text that Contains from the Conditional Formatting.

Change Text Color Based on Value by Applying Built-in Conditional Formatting

Step 3:

  • Type John in the text box.
  • Also, set the Custom Format as previously shown.

Change Text Color Based on Value by Applying Built-in Conditional Formatting

Step 4:

  • Then press OK.

Here, the mentioned text color is changed.

Read More: Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)


2. Change Text Color Using Format Cells Command from the Context Menu

In this method, we will use the Format Cells command to change the color of the text.

Step 1:

  • First, select the cells of the Balance column.

Step 2:

  • Now, right-click on the selected cells.

Change Text Color Using Format Cells Command from the Context Menu

Step 3:

  • Select the Format Cells from the command list. We get the Format Cells window.
  • Click on the Number tab > the Number category.
  • Then select the suitable number format for negative numbers.

Change Text Color Using Format Cells Command from the Context Menu

Step 4:

  • Finally, press OK.

This method is only applicable for negative and positive numbers. Here the color of the negative numbers is changed.

Read More: 4 Quick Excel Formula to Change Cell Color Based on Date


3. Use VBA Macros to Change Text Color Based on Value

In this section, we will apply the VBA codes to change the color of text based on value. See the following steps.

Step 1:

  • First, go to the Home tab.
  • Then click Record Macro from the Developer tab.

Use VBA Macros to Change Text Color Based on Value

Step 2:

  • Put Text_Color as the Macro name.
  • Then press OK.

Step 3:

  • Then step in the Text_Color macro.
  • Or click Alt+F11 to enter the command module.
  • Now, write the following code on the VBA command module.
Sub Text_Color()
Dim WorkRng As Range
xTitleId = "Date Range"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each cell In WorkRng
If cell.Value > 0 Then
cell.Font.Color = vbGreen
ElseIf cell.Value < 0 Then
cell.Font.Color = vbRed
End If
Next
End Sub

Use VBA Macros to Change Text Color Based on Value

Step 4:

  • Then, press F5 to run the code.
  • A dialog box will show the input range.
  • We select a range.

Step 5:

  • Now, press OK.

We set conditions on the VBA code to change the color of the negative and positive numbers. And as a result, color-changing is showing.

Read More: Excel Conditional Formatting Based on Multiple Values of Another Cell


Conclusion

In this article, we described the Excel formula to change the text color based on the value. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo