How to Create and Use ColorFunction in Excel (With Easy Steps)

In Excel, there is no function to make calculations based on the color of the cells. But by using Microsoft Visual Basic for Applications (VBA) it is possible to make custom functions for calculation based on the color of the cells. In this article, I will show you how to create and use the ColorFunction in Excel with easy steps.

Download Practice Workbook

Get this sample file and practice the methods yourself.

Step-by-Step Process to Create and Use ColorFunction in Excel

Let’s say, we have a dataset of the product order of a company. The column Order Quantity is colored based on their delivery status. Delivered orders are colored green and orders which are in delivery are colored orange. Now we can find the number of customers who either received or didn’t receive their orders by counting the same colored cells. The total quantity of Order Delivered or Order in Delivery can be found by adding up the same colored cells.

Now, let’s follow the steps below to do the task.

Step 1: Open Macro Module in VBA Editor Window

First, you have to open the VBA window.

• For this, go to the Developer tab and select Visual Basic to open the VBA Editor window.
• Otherwise, you can open it by pressing Alt + F11 on your keyboard.

• After that, right-click on the sheet name from the left panel and go to Insert > Module.

• As a result, a Module box will be opened.

Step 2: Insert VBA Code to Create ColorFunction

Insert the following code, in the Code window, opened with Module and close the VBA Editor.

Function Color_Function(rClr As Range, rRng As Range, Optional SUM As Boolean)
Dim rCl As Range
Dim lColm As Long
Dim vRslt
lColm = rClr.Interior.ColorIndex
If SUM = True Then
For Each rCl In rRng
If rCl.Interior.ColorIndex = lColm Then
vRslt = WorksheetFunction.SUM(rCl, vRslt)
End If
Next rCl
Else
For Each rCl In rRng
If rCl.Interior.ColorIndex = lColm Then
vRslt = 1 + vRslt
End If
Next rCl
End If
Color_Function = vRslt
End Function

Step 3: Save Workbook as Excel Macro-Enabled Workbook

You have to save the Excel workbook in .xlsm format to save your custom function.

• First, go to the File tab from the upper left corner of your Excel Ribbon.

• After that, Select Save As.
• Then select Excel Macro-enabled Workbook (*xlsm) and click on Save.

Step 4: Use ColorFunction to Count Colored Cells

Finally, we are at that stage where we can use the custom ColorFunction.

• First, to get the count of green cells, type the following formula in Cell C17.

• Then, press Enter and you will get the count for green cells.

• In a similar manner, you can get the count for orange cells.

Step 5: Apply ColorFunction to Sum Colored Cells

In this last step, we will calculate the sum value of each colored cell with the ColorFunction.

• First, to get the sum of green color cells, type the following formula in Cell D17.

• Finally, you will get the sum of green cells by pressing Enter.

• In a similar manner, you can get the sum of orange cells.

Alternative Solution of ColorFunction in Excel

Instead of using ColorFunction, you can get the count of colored cells or the sum of the colored cells in the following two different ways.

1. Apply SUBTOTAL Function to Count Colored Cells

You can get the count and sum of colored cells by using the Filter feature and the SUBTOTAL function.

• First, select any cell from the dataset.
• Then, go to the Data tab and click on Filter.

• As a result, a downward arrow will appear beside your every column header.
• Now, click on the downward arrow beside the Order Quantity (Colored column) > go to Filter by Color > select the green color.

• Afterward, you will only see light green colored data in this column.
• Next, get the count with the following formula.

• After this, press Enter to get the count of green color cells.

• Following this, type the following formula to get the sum of the colored cells.

• After pressing Enter, you will get the sum of green color cells.

• By changing the filter to orange color, you can get the count and sum of orange-colored cells.

2. Insert GET.CELL Function for Counting Colored Cells

By using the GET.CELL function you can also count and sum up the colored cells.

• First, go to the Formulas tab and select Define Name.

• As a result, the New Name box will appear.
• Here, type a name such as Color in the Name box.
• Afterward, insert the formula in Refers to box and press OK.

• Now, type =Color (Name you gave previously in the Define Names box) in Cell F5.

• After this, press Enter and use the AutoFill tool to get the color codes of all of your colored cells of Column F.

• Now type the following formula to get the count of green color cells:

• After pressing Enter, you will get the count of green-colored cells.

• Next, type the following formula to get the sum of green-colored cells,

• Lastly, press Enter and you will get the sum of all green-colored cells.

• In a similar way, you can get the count and sum of orange-colored cells.

Conclusion

I hope after going through the article now you will understand how to create and use ColorFunction in Excel with easy steps. If you face any difficulties please leave a comment. Follow ExcelDemy for more tutorials.