How to Change Text Color Based on Value with Excel Formula

Fortunately, 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 3 suitable ways to change text color based on the value with Excel Formula with appropriate illustrations.

overview of excel formula to change text color based on value

Here, we have used Excel Formula in Conditional Formatting to change text color based on value. Read through the rest of the part to learn more along with some other methods in detail.


Download Practice Workbook

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


3 Ways to Change Text Color Based on Value with Excel Formula

Here, 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. Usually, Conditional Formatting can work only with functions that return only TRUE or FALSE. Example: OR, ISODD, COUNTIF, etc. functions.

Sample Dataset showing employee balance in usd

Usually, no native Excel formula alone can change the text color. However, 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.


1. Using ISODD Function

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.

Steps:

  • First, select the column where you want to change text color.
  • Now, go to the Home tab.
  • Then, select the New Rule from the Conditional Formatting.

insert new rule in conditional formatting option

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

use ISODD formula in Conditional Formatting

  • Now, select the font color and press OK.

Format cells dialog box

  • Here, we will get a Preview of the font color.

Using ISODD Function to change text color based on value in excel

  • Finally, press OK and we can see that the odd numbers on the Balance column are showing with changing color.

excel formula to change text color based on value final result

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


2. Changing Text Color Based on Value with OR Function

Furthermore, we will use the OR function here. Hence, follow the steps below.

Steps:

  • Firstly, select the desired column and go to the New Rule option as shown previously.
  • Now, write the formula:
=OR(B5="John",C5>400)
  • Also, select the format like previously.

choosing Text Color Based on Value with OR Function in Excel

  • Lastly, press OK and we see that the text color is changed.

Final result to change Text Color Based on Value with OR Function

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


Similar Readings


3. Applying COUNTIF Function to Change Text Color Based on Value

The COUNTIF function is used to count something with given criteria. Hence, follow the steps below.

Steps:

  • Initially, we modify the data set for applying the mentioned formula.

sample dataset for inserting countif function

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

Applying COUNTIF Function to Change Text Color Based on Value in Excel

  • Finally, press OK and we see that the matching names of column B are turned red as we mentioned.

Final output for inserting countif function


Similar Readings


How to Apply VBA Code 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. However, 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.

Steps:

  • Firstly, hold the Alt + F11 keys in Excel, which opens the Microsoft Visual Basic Applications window.
  • Secondly, click the Insert button and select Module from the menu to create a module.

insert module in vba window

  • Now, write the following code on the VBA command module.
Sub Text_Color()
Dim WorkRng As Range
xTitleId = "ExcelDemy"
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

VBA Code to Change Text Color Based on Value in Excel

VBA Code Breakdown

  • Initially, we create a new procedure Sub in the worksheet using the below statement.

Sub Text_Color()

  • Then, we declare variables as below.
Dim WorkRng As Range
xTitleId = "ExcelDemy" 
  • Thirdly, we set the variables and select the range.
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
  • After that, we activate it and started an IF loop for each cell. However, it checks the value and provides preset color.
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
  • Lastly, we end the Sub of the VBA macro as
End Sub
  • Fourthly, press the F5 key to run the code.
  • After that, a dialog box will show the input range and we select a range.

vba code range dialog box

  • Finally, press OK and you will get your desired output.

Result after applying vba code in excel

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


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

practice section


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 Paul

Alok Paul

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.

1 Comment
  1. I am a containment Engineer. I want a formula for Excel to return the words replace, review when the result cell shows “Fail”.I’d like the text to turn red as a visual aid.
    I just cant get one to work.
    I’m new to Excel.
    Can you help me please.
    Best regards
    [email protected]

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo