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.

dataset

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

vba module

As a result, a Module box will be opened.

module box

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

vba code

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.

opening file

After that, Select Save As.

save as

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

save as xlsm

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

ColorFunction

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

ColorFunction

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

ColorFunction

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.

ColorFunction

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

ColorFunction

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

ColorFunction

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.

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.

filter by 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.

subtotal

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

colorfunction

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

subtotal

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

sum up

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

color filter

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.

Name box

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)

define name

Now type =Color (Name you gave previously in the Define Names box) in the adjacent column of your colored column.

colorcode

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.

color code

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.

countif

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

ColorFunction

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.

sumif

Press ENTER  and you will get the sum of all green colored cells.

ColorFunction

In a similar way, you can get the count and sum of light orange colored cells.

changed color

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

Leave a reply

ExcelDemy
Logo