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
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:
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.
Read More: How to Change Text Color with Formula in Excel (2 Methods)
Similar Readings:
- Excel Conditional Formatting Text Color (3 Easy Ways)
- Apply Conditional Formatting to Each Row Individually: 3 Tips
- How to Do Conditional Formatting with Multiple Criteria (11 Ways)
- Pivot Table Conditional Formatting Based on Another Column (8 Easy Ways)
- Conditional Formatting with INDEX-MATCH in Excel (4 Easy Formulas)
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.
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
- Conditional Formatting for Blank Cells in Excel (2 Methods)
- How to Compare Two Columns Using Conditional Formatting in Excel
- Conditional Formatting on Text that Contains Multiple Words in Excel
- How to Remove Conditional Formatting but Keep the Format in Excel
- Excel Formula to Change Text Color Based on Value (+ Bonus Methods)