Change Font Color Based on Value of Another Cell in Excel

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.

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

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:

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, Add a criteria table like the image below.

Then, Select the range of cells B5:D12.

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

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

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.

So, our formula and font color is set.
Now, Click OK.

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

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.

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

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

Now, press Alt+F8 to open the Macro dialog box. Select format_all_rows.
After that, Click on Run.

And all the students with Marks less than 33 appear in color.

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.

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

Download Practice Workbook

You can download this practice workbook.

Change Font Color Based on Another Cell.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!