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

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

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

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

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

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

**$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)`

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

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.