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

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

### 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)`

Here, Cell F5 is the Criteria cell whose color will be counted. \$E\$5:\$E\$11 is the Range for the count. FALSE indicates that the number of cells having the same color as the criteria cell will be counted.
• 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)`

Here, Cell F5 is the Criteria cell whose color will be summed. \$E\$5:\$E\$11 indicates the Range for sum. TRUE indicates that the number of cells having the same color as the criteria cell will add.
• 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)`

Here, we used the SUBTOTAL function to count the selected colored cell. In the formula, 2 is the function_num argument to count and E5:E11 is the range of data.
• 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)`

Here, 9 indicates that the cell will be added up and E5:E11 is the range of data.
• 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)`

In this formula, 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).
• 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)`

Here, \$F\$5:\$F\$11 is the range for the count, and 50 is the color code of green in the COUNTIF function.
• 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)`

Here, F5:F11 is the criteria range and 50 is the color code of light green and E5:E11 is the sum range under the SUMIF function to calculate the sum.
• 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

1. 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.

2. 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.

Advanced Excel Exercises with Solutions PDF