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.
=Color_Function(C13,$E$5:$E$11,FALSE)
- 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.
=Color_Function(C13,$E$5:$E$11,TRUE)
- 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.
=SUBTOTAL(2,E5:E11)
- 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.
=SUBTOTAL(9,E5:E11)
- 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.
=GET.CELL(38,GET.CELL!$E5)
- 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:
=COUNTIF($F$5:$F$11,50)
- 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,
=SUMIF($F$5:$F$11,50,$E$5:$E$11)
- 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.
hello, I am quite interested in this colour counter as I use colours to see records easily. I preferred to use your first solution with VBasic. However, I have run into problems which you might be able to help me with.
When trying to run the created function, I get an error message:
No RETURN() or HALT () function found on macro sheet.
I might just try a new file and start from scratch to see what happens then.
Hello Paul,
I hope you have found your solution already. If not, please share your Excel workbook with me. It is difficult to say the reason for the error message without seeing your worksheet or code. I will try my best to solve your problem.
I have tried option one as well and my function returns the number of cells. It doesn’t count the cells that are the same color. Did i do something wrong?
Hello Chris, in option one, you need to insert the VBA code first. After that, you can apply the ColorFunction. Otherwise, you will face difficulties.
If you don’t get the solution yet, you can send your worksheet. We will take a look closely and find the required solution.