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

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

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

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

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

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