Change Font Color Based on Value of Another Cell in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Changing Font Color Based on Value of Another Cell in Excel


Download Practice Workbook

You can 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:

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

Adding Criteria Table to Change Font Color Based on Value of Another Cell in Excel

  • Then, Select the range of cells B5:D12.

Selecting Data Range to Change Font Color Based on Value of Another Cell in Excel

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

Selecting New Rules to Change Font Color Based on Value of Another Cell in Excel

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

Choosing Rule Type to Change Font Color Based on Value of Another Cell in Excel

  •  After that, Type the following formula in the field:
=$D5<$F$5
  • Now, Click on Format.

Inserting Condition in Conditional Formatting to Change Font Color Based on Value of Another Cell in Excel

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

Setting Font Color to Change Font Color Based on Value of Another Cell in Excel

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

Steps for Conditional Formatting

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

Result Output by Conditional Formatting

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.

Selecting First Row

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

Setting New Rules

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

Choosing Rule Type

  • After that, type the following formula in the field:
=$D5<$F$5
  • Now, Click on Format.

Setting Condition for Conditional Formatting

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

Setting Font and Color

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

Steps for Conditional Formatting

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

Inserting VBA Code in Macro

  • After that, Select range of cells B5:D12

Selecting Data Range

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

Running VBA Code

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

Using VBA Code to Change Font Color Based on Value of Another Cell in Excel

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

A.N.M. Mohaimen Shanto

A.N.M. Mohaimen 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo