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.

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.

**Table of Contents**Expand

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

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.

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

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

- Now, select the font color and press
**OK**.

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

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

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

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

**Read More:** Change Font Color Based on Value of Another Cell in Excel

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

- Now, write the formula in the
**New Rule**option shown previously. - The formula will be:

`=COUNTIF(C5,B5)>0`

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

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

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

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

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

**Download Practice Workbook**

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

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

## Related Articles

- How to Format Cell Based on Formula in Excel
- How to Use Conditional Formatting Based on VLOOKUP in Excel
- How to Apply Conditional Formatting with INDEX-MATCH in Excel
- Excel Conditional Formatting Formula with IF
- Excel Conditional Formatting Formula If Cell Contains Text
- Applying Conditional Formatting for Multiple Conditions in Excel
- Conditional Formatting If Cell is Not Blank

- Conditional Formatting Multiple Text Values in Excel
- Conditional Formatting Entire Column Based on Another Column in Excel
- Excel Highlight Cell If Value Greater Than Another Cell

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

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]

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 C5contains textFail, then the formula will returnReplace, Review. If not, then the result it will return will be blank.Formula Breakdown●

TEXT(“Replace, Review”, “[$-409]@”)This formula returns

Replace, Revieweach time.●

IF(C5=”Fail”, TEXT(“Replace, Review”, “[$-409]@”), “”)This formula returns

Replace, Reviewonly for the text stringFail.Now to add

redcolor to the output, use theconditional formattingfeature as illustrated in this article.● Select the

range D5:D9.● Go through these steps:

Home >> Conditional Formatting >> New Rule.● Now, select

Format only cells that containfrom theSelect a Rule Typesection.● Select

Specific Text.● Type

Replace, Review.● Click on

Format.● Select

Font.● Click on the

arrow downicon in thecolorsection.● Select the color

red.● Click on

OK.● Click on

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

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