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

Changing the font color of a dataset is one of the essential tasks of Microsoft Excel. We do it to have a clear and attractive look at our dataset. It helps us to visually analyze the data. Now, you may need to change the font color based on some conditions. In this tutorial, you will learn to change font color based on the value of another cell in Excel. It will be with suitable examples and proper illustrations.


Download Practice Workbook

Download this practice workbook.


2 Useful Methods to Change Font Color Based on Value of Another Cell in Excel

In the upcoming sections, we are going to provide you with two essential and effective methods to change font color based on the value of another cell in Excel. We recommend you learn and apply all these methods to your dataset. I hope it will enhance your Excel knowledge.

To demonstrate this tutorial, we are going to use this dataset:

dataset of Change Font Color based on Another Cell excel

As you can see, we have a dataset of some students and their marks on the final exam. Besides, we have another cell that shows the passing score. Now, our goal is to find the students who scored less than 33. That describes, we have to identify who failed the exam. So, let’s dive into it.

1. Use of Conditional Formatting to Change Font Color Based on Value of Another Cell

If you are well known for Excel, you know we will include that. When you have to format a cell with a condition, the first thing that comes to mind is Conditional Formatting. It is one of the essential tools of Excel. In this section, we are also going to use that. Just follow these simple steps to solve your problem.

📌 Steps

First, select the range of cells B5:D12.

select range of cells

Then, from the Home tab, select Conditional Formatting > New Rule.

Use of Conditional Formatting to Change Font Color Based on Value of Another Cell

Then, from the New Formatting Rule dialog box, select Use a formula to determine which cells to format option.

After that, type the following formula in the field:

=$D5<$F$5

Use of Conditional Formatting to Change Font Color Based on Value of Another Cell

Now, click on Format.

Then, from the Format Cells dialog box select the Font option. Then, choose any color from the dropdown menu. After that, click on OK.

Now, our formula and font color is set.

Use of Conditional Formatting to Change Font Color Based on Value of Another Cell

Now, click OK.

Excel Change Font Color Based on Value of Another Cell

As you can see, we are successful to change font color based on the value of another cell in Excel.

Read More: How to Change Text Color with Formula in Excel (2 Methods)


Similar Readings:


2. VBA Codes to Change Font Color of a Range in Excel

Now, if you are a VBA freak like me, you may like this method. Yes, you can solve this problem with conditional formatting. But, you know, some people love to solve problems with coding. So, if you are one of them, try this method.

Before I start, let me be clear. We are also using conditional formatting. We will do this for one row. And by the VBA codes, we will paste it across all the rows of our selection.

📌 Steps

First, select the range of cells B5:D5.

Then, from the Home tab, select Conditional Formatting > New Rule.

VBA Codes to Change Font Color of a Range in Excel

Then, from the New Formatting Rule dialog box, select Use a formula to determine which cells to format option.

After that, type the following formula in the field:

=$D5<$F$5

=$D5<$F$5

Now, click on Format.

Then, from the Format Cells dialog box select the Font option. Then, choose any color from the dropdown menu. After that, click on OK.

Now, our formula and font color is set.

Use of Conditional Formatting to Change Font Color Based on Value of Another Cell

Now, click OK.  After that, the first row will be formatted. Now, we have to copy that across all the rows.

Now, press Alt+F11 on your keyboard to open the VBA editor. Then, click on Insert > Module. 

insert vba module

Then, type the following code:

Sub format_all_rows()

Dim rng As Range
Dim r As Long

Set rng = Selection
rng.Rows(1).Copy

For r = 2 To rng.Rows.Count
 rng.Rows(r).PasteSpecial Paste:=xlPasteFormats
Next r

End Sub

  Now, save the file. After that, select range of cells B5:D12

select range of cells

Now, press Alt+F8 to open the Macro dialog box. Select format_all_rows.

VBA Codes to Change Font Color of a Range in Excel

After that, click on Run.

Excel Change Font Color Based on Value of Another Cell

As you can see, after using the VBA codes, we are successful to change the font color based on the value of another cell in Excel. I hope it will come in handy in the future.

Read More: VBA Conditional Formatting Based on Another Cell Value in Excel


💬 Things to Remember

The VBA codes are just to copy the formats. It will individually create that formula for every row. So, if your dataset is large, it may slow down a little bit. But, it will save a lot of time.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to change font color based on the value of another cell in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo