Alok
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.
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.
Step 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.
Step 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.
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.
Step 2:
- Now, go to manage the rules shown previously to specify the area.
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:
- Excel Conditional Formatting Based on Date Range
- Excel Conditional Formatting If a Cell Contains Any Text
- How to Do Conditional Formatting for Multiple Conditions (8 Ways)
- Conditional Formatting with INDEX-MATCH in Excel (4 Easy Formulas)
- Apply Conditional Formatting to the Overdue Dates in Excel (3 Ways)
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
Step 3:
- Then go to the manage rule to set the area.
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.
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.
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.
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.
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.
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.
Step 2:
- We set a value in the Less Than window.
- Also, set a Custom Format.
Step 3:
- Now, press OK.
Here, 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.
Step 3:
- Type John in the text box.
- Also, set the Custom Format as previously shown.
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.
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.
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.
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
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.
