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

Method 1 – Using ISODD Function

Steps:

  • Select the column where you want to change the text color.
  • Go to the Home tab.
  • Select the New Rule from the Conditional Formatting.

insert new rule in conditional formatting option

  • Select Use a formula to determine which cells to format as the Rule Type.
  • Write the formula as mentioned in the image. The formula is:
=ISODD(C5)
  • Click Format.

use ISODD formula in Conditional Formatting

  • Select the font color and press OK.

Format cells dialog box

  • Get a Preview of the font color.

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

  • 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


Method 2 – Changing Text Color Based on Value with OR Function

Steps:

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

choosing Text Color Based on Value with OR Function in Excel

  • Press OK and we see that the text color is changed.

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


Method 3 – Applying COUNTIF Function to Change Text Color Based on Value

Steps:

  • Modify the data set for applying the mentioned formula.

sample dataset for inserting countif function

  • 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

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

Final output for inserting countif function


How to Apply VBA Code to Change Text Color Based on Value

Steps:

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

insert module in vba window

  • 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

  • Create a new procedure Sub in the worksheet using the below statement.

Sub Text_Color()

  • Declare variables as below.
Dim WorkRng As Range
xTitleId = "ExcelDemy" 
  • Set the variables and select the range.
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
  • Activate it and start an IF loop for each cell. However, it checks the value and provides a 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
  • End the Sub of the VBA macro as
End Sub
  • Press the F5 key to run the code.
  • A dialog box will show the input range and we select a range.

vba code range dialog box

  • Press OK and you will get your desired output.

Result after applying vba code in excel


Download Practice Workbook

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


Related Articles

<< Go Back to Conditional Formatting with Multiple Conditions | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

2 Comments
  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]

    • Reply Avatar photo
      Raiyan Zaman Adrey Jul 27, 2023 at 11:43 AM

      Dear COLYNN BURRELL,

      Thanks for reading our article. Try the following formula to resolve your issue.
      =IF(C5="Fail", TEXT("Replace, Review", "[$-409]@"), "")

      The formula indicates that if cell C5 contains text Fail, then the formula will return Replace, Review. If not, then the result it will return will be blank.

      Dataset

      Formula Breakdown
      TEXT(“Replace, Review”, “[$-409]@”)
      This formula returns Replace, Review each time.
      IF(C5=”Fail”, TEXT(“Replace, Review”, “[$-409]@”), “”)
      This formula returns Replace, Review only for the text string Fail.

      Now to add red color to the output, use the conditional formatting feature as illustrated in this article.
      ● Select the range D5:D9.
      ● Go through these steps: Home >> Conditional Formatting >> New Rule.

      conditional formatting

      ● Now, select Format only cells that contain from the Select a Rule Type section.
      ● Select Specific Text.
      ● Type Replace, Review.
      ● Click on Format.

      new formatting rule bar

      ● Select Font.
      ● Click on the arrow down icon in the color section.
      ● Select the color red.
      ● Click on OK.

      selecting color

      ● Click on OK again.

      formatting

      ● And the output will be as follows as you have desired.

      output

      Note: However, you can’t change the color of the output just after applying the formula. You have to use conditional formatting for that.

      If the problem still appears, kindly send us your Excel file and specify the exact problem you are facing.
      Thank you

      Best Wishes,
      Raiyan Zaman Adrey

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo