# How to Create and Use ColorFunction in Excel

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.

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

#### Prantick

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.

We will be happy to hear your thoughts