It is possible to make custom functions for calculation based on the color of the cells using the Microsoft Visual Basic for Applications (VBA).
Download Practice Workbook
Practice with this sample.
Creating and Using the Color Function in Excel
The sample dataset showcases the product order record of a company. The column Order Quantity is colored based on the delivery status. Delivered orders are colored green and orders which are in delivery are colored orange.
Step 1 – Open a Macro Module in the VBA Editor Window
- Go to the Developer tab and select Visual Basic to open the VBA Editor or press Alt + F11.
- Right-click the sheet name and select Insert > Module.
The Module box will be displayed.
Step 2 – Insert the VBA Code to Create the Color Function
- Enter the following code 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 the Workbook as an Excel Macro-Enabled Workbook
- Go to the File tab.
- Select Save As.
- Choose Excel Macro-enabled Workbook (*xlsm) and click Save.
Step 4 – Use the Color Function to Count Colored Cells
- To get the count of green cells, enter the following formula in C17.
=Color_Function(C13,$E$5:$E$11,FALSE)
Here, F5 is the Criteria. $E$5:$E$11 is the Range. FALSE indicates that cells having the same color as the criteria will be counted.
- Press Enter to see the result.
- You can also count the orange cells.
Step 5 – Apply the Color Function to Sum Colored Cells
- To see the sum of green cells, enter the following formula in D17.
=Color_Function(C13,$E$5:$E$11,TRUE)
Here, CF5 is the Criteria. $E$5:$E$11 indicates the Range. TRUE indicates that the number of cells having the same color as the criteria cell will be added.
- Press Enter to see the result.
- You can follow the same steps for the orange cells.
Alternative Solution to Color Function in Excel
1. Apply the SUBTOTAL Function to Count Colored Cells
- Select any cell in the dataset.
- Go to the Data tab and click Filter.
- Click the downward arrow beside the Order Quantity (Colored column) > go to Filter by Color > select green.
- You will only see light green colored data in this column.
- To count, use the following formula.
=SUBTOTAL(2,E5:E11)
2 is the function_num argument to count and E5:E11 is the data range.
- Press Enter to see the sum of the green cells.
- Enter the following formula to see the count of the colored cells.
=SUBTOTAL(9,E5:E11)
Here, 9 indicates that the cell will be added and E5:E11 is the data range.
- Press Enter, to see the sum of green cells.
- By changing the filter to orange, you can get the count and the sum of orange-colored cells.
2. Insert GET.CELL Function to Count Colored Cells
- Go to Formulas and select Define Name.
- In the New Name box, enter a name (Color).
- Enter the formula in Refers to and click OK.
=GET.CELL(38,GET.CELL!$E5)
38 indicates that the formula will give the color code of the referred cell and GET.CELL!$E5 is the referred cell (the first cell after the column header of the colored column).
- Enter =Color in F5.
- Press Enter and use the AutoFill to get the color codes of all cells in Column F.
- Enter the following formula to get the count of green cells:
=COUNTIF($F$5:$F$11,50)
Here, $F$5:$F$11 is the range, and 50 is the color code of green in the COUNTIF function.
- Press Enter to get the count of green-colored cells.
- Enter the following formula to get the sum of green-colored cells.
=SUMIF($F$5:$F$11,50,$E$5:$E$11)
Here, F5:F11 is the criteria range, 50 is the color code of light green and E5:E11 is the sum range in the SUMIF function.
- Press Enter to see the sum of all green-colored cells.
- Follow the same steps to get the count and sum of orange-colored cells.
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.