How to Use IF Statement Based on Cell Color in Excel

Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that you can use an IF statement based on cell color in Excel for better operation. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you 3 suitable examples of an IF statement based on cell color in Excel. Hence, read through the article to learn more and save time.


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


3 Uses of IF Statement Based on Cell Color in Excel

In this tutorial, I will show you some examples of the IF statementΒ based on cell color and how the process becomes more interesting with Excel. In addition, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. However, it includes several real-life examples that we regularly use in Excel. Here, I have used 3 different examples to help you understand the scenario properly. For the purpose of demonstration, I have used different sample datasets for each example, and it will help you to identify the differences between the scenarios easily. Here, you have to Define Names for the Color Code of cells for all 3 examples initially.


πŸ“Œ Example 1: Calculate Net Price with Excel IF Statement Based on Cell Color

First of all, I will show you an easy example of the if statement based on cell color in Excel. For the purpose of demonstration, I have used the following sample dataset. Here, it contains some Products and their Quantity. However, the rows contain multiple colors and I will calculate the Net Price of some specific products using the Color Code. Hence, go through the steps below in order to complete the operation properly.

Calculate Net Price with Excel If Statement Based on Cell Color

πŸ“Œ Steps:

  • Firstly, from the Formulas tab, select Define Name.

  • Secondly, a box will appear, and write a name (in this case I wrote CellColor) in the Name: section of the box.
  • Thirdly, write the following formula in Refers to and press OK.

=GET.CELL(38,'Example 1'!B5)

  • Fourthly, select cell D5 and insert the formula mentioned below.

=CellColor

  • Now, utilize the AutoFill tool for the entire column.

  • Again, select cell E5 and insert the formula below.

=IF(CellColor=40,C5*$E$12,"-")

  • Similarly, use the AutoFill tool for the whole column.

Excel If Statement Based on Cell Color

Read More: How to Use Conditional Formatting If Statement Is Another Cell


Similar Readings


πŸ“Œ Example 2: Find Discounted Price Based on Cell Color Using IF Statement

Furthermore, through this example, I will show the steps to find the discounted price using the if statement based on cell color in Excel. For the purpose of demonstration, I have selected the following sample dataset. Here, the dataset contains two columns, which include Item and Price. However, a particular amount of discount is set to 10% and I will find out the Discounted Price of some specific items. In addition, the output will show the discounted price or No Discount. So, go through the steps mentioned below.

Find Discounted Price with Excel If Statement Based on Cell Color

πŸ“Œ Steps:

  • Initially, define another name similarly with the formula below.

=GET.CELL(38,'Example 2'!B5)

  • Then, apply the formula mentioned below in cell D5.

=ColorCell

  • Next, utilize the AutoFill tool to apply the formula to the whole column.

  • After that, select cell E5 and write the formula below.

=IF(ColorCell=39,C5-C5*$E$12,"No Discount")

  • Finally, apply the AutoFill tool to the entire column in order to get the discounted price.

Excel If Statement Based on Cell Color

Read More: How to Find Sum If Cell Color Is Green in Excel (4 Easy Methods)


πŸ“Œ Example 3: Utilize IF Statement to Compute Bonus on Sales

Last but not least, I will show the monthly bonus on sales. For the purpose of demonstration, I have the following dataset containing the Name of salesmen and their Sales. Here, the name which has a specific cell color will get a 12% bonus on their sales. However, I will find the Color Code using the Define Name feature and calculate the amount of the Bonus. Additionally, the output will show the amount of bonus for applicable salesmen otherwise zero. Hence, read through the steps below in order to complete the task properly.

Compute Bonus on Sales with Excel If Statement Based on Cell Color

πŸ“Œ Steps:

  • In the beginning, define another name following a similar process.
  • Then, apply the formula mentioned below in cell D5 in order to get the Color Code.

=ColorCode

  • Next, utilize the AutoFill tool for the entire column.

  • Afterward, select cell E5 and insert the formula below.

=IF(CellColor=40,C5*$E$12,0)

  • At last, use the AutoFill tool for the whole column in order to find the bonus for the selected salesmen.

Excel If Statement Based on Cell Color

Read More: How to Check If Cell Contains One of Several Values in Excel


πŸ’¬ Things to Remember

  • Firstly, you have to use different colors in the case of applying VBA Macro.
  • Secondly, you have to save the Excel file with the .xlsm suffix in case of the file has VBA Macro codes within it.
  • Thirdly, you can choose the colors according to your personal preference.
  • Fourthly, you have to Define Name for each example.
  • Lastly, the output will show #NAME? error if you don’t insert the name of CellColor properly.

Conclusion

These are all the steps you can follow to use an IF statement based on cell color in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit Exceldemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo