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 you can create and use the 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 with light green and orders which are in delivery are colored with light 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.

**Table of Contents**hide

## Download Practice Workbook

## ColorFunction in Excel

**Step 1**: Opening Macro Module in VBA window

First, you have to open the** VBA** window by pressing **ALT+F11. **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**: Inserting VBA code for creating ColorFunction

Insert the following code, in the **Module** box and close the **VBA** window.

```
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
```

Here, the VBA code creates a custom function named **ColorFunction **which will count colored cells if we give the argument as **FALSE** and will sum up the colored cells if we give the argument as **TRUE.**

**Step 3**: Saving the workbook as Excel Macro Enabled Workbook

You have to save the Excel workbook in **.xlsm** format to save your custom function. First, go to **File **from the upper left corner of your Excel window.

After that, Select** Save As.**

Then select **Excel Macro-enabled Workbook (*xlsm) **and click on **Save.**

**Step 4**: Using ColorFunction to Count Colored cells

Now you can use your custom **ColorFunction.**

To get the count of light green cells, type the following formula in an empty cell,

`=ColorFunction(F5,$D$5:$D$11,FALSE)`

Here, **F5 **= Criteria cell which color will be counted

**$D$5:$D$11 **= Range for count

**FALSE **indicates that the number of cells having the same color with the criteria cell will be counted

Press **ENTER ** and you will get the count for light green cells.

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

**Step 5**: Using ColorFunction to Sum Colored Cells

To get the sum of light green color cells, type the following formula in an empty cell,

`=ColorFunction(F5,$D$5:$D$11,TRUE)`

Here, **F5 **= Criteria cell which color will be summed

**$D$5:$D$11 **= Range for sum

**TRUE **indicates that the number of cells having the same color with the criteria cell will be added up.

** **

you will get the sum of light green cells by pressing **ENTER.**

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

### Alternative Ways to Count and Sum Colored Cell

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

#### 1. Using FILTER and SUBTOTAL Functions

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

First, go to the **Data **tab and click on **Filter.**

After that downward arrow will appear beside your every column header. Click on the downward arrow beside the *Order Quantity *(Colored column), go to **Filter by Color**, and select the light green color.

Now you will only see light green colored data in this column. To get the count type the following formula,

`=SUBTOTAL(2,D5:D11)`

Here **2 **indicates that the cell will be counted and **D5:D11 **is the range of data.

After pressing **ENTER **you will get the count of light green color cells.

To get the sum of the colored cells, type the following formula,

`=SUBTOTAL(9,D5:D11)`

Here **9 **indicates that the cell will be added up and **D5:D11 **is the range of data

After pressing **ENTER **you will get the sum of light green color cells.

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

#### 2. Using GET.CELL Function

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

A **New Name **box will appear. Type a name such as *Color *in the **Name **Box. and Insert the formula in **Refers to **box and press **OK.**

`=GET.CELL(38,’GET CELL’!$D5)`

Here 38 indicates that the formula will give the color code of the referred cell and **’GET CELL’!$D5 **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 the adjacent column of your colored column.

After pressing **ENTER **and dragging cell **E5 **to the end of your dataset, you will get the color codes of all of your colored cells of column **E**.

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

`=COUNTIF($E$5:$E$11,50)`

Here, **$E$5:$E$11 **is the range for count, and 50 is the color code of light green.

After pressing **Enter** you will get the count of green colored cells.

Type the following formula to get the sum of light green colored cells,

`=SUMIF(E5:E11,50,D5:D11)`

Here, **E5:E11 **is the criteria range, 50 indicates the color code of light green and **D5:D11 **is the sum range.

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 light orange colored cells.

## Conclusion

I hope after going through the Article now you can create and use ColorFunction in Excel. If you face any problem please leave a comment.

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.