# How to Use IF Statement Based on Cell Color in Excel

## 3 Uses of IF Statement Based on Cell Color in Excel

### Example 1 – Calculate the Net Price Based on Cell Color

The sample dataset contains some Products and their Quantity. The rows contain multiple colors, and we will calculate the Net Price of some specific products using the Color Code.

Steps:

• From the Formulas tab, select Define Name.

• A box will appear.
• Write a name (in this case I wrote CellColor) in the Name: section of the box.
• Use the following formula in Refers to and press OK.

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

• Select cell D5 and insert the following formula:

`=CellColor`

• Use the AutoFill tool for the entire column.

• Select cell E5 and insert the formula below.

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

• Use the AutoFill tool for the whole column.

### Example 2 – Find the Discounted Price Based on Cell Color

The dataset contains two columns, Item and Price. The discount is set to 10% and we will find out the Discounted Price of some specific items.

Steps:

• Define another name with the formula below.

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

• Apply the following formula in cell D5.

`=ColorCell`

• Use the AutoFill tool to apply the formula to the whole column.

• Select cell E5 and insert this formula.

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

• Apply the AutoFill tool to the entire column in order to get the discounted price.

### Example 3 – Compute Bonus on Sales

We have a dataset containing the Names of salespeople and their Sales. The names which have a specific cell color will get a 12% bonus on their sales. Otherwise, the bonus will be zero.

Steps:

• Define another name for ColorCode following a process similar to the one in Example 1.
• Apply the following formula in cell D5 to get the Color Code.

`=ColorCode`

• Use the AutoFill tool for the entire column.

• Select cell E5 and insert the following formula.

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

• Use the AutoFill tool for the whole column in order to find the bonus for the selected salesmen.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF