How to Create and Use the Color Function in Excel – Easy Steps

It is possible to make custom functions for calculation based on the color of the cells using the Microsoft Visual Basic for Applications (VBA).

ColorFunction Excel


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.

ColorFunction Excel

 

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.

Selecting Visual Basic from Developer Tab

  • Right-click the sheet name and select Insert > Module.

Opening Module to input code from Insert Tab

The Module box will be displayed.


Step 2 – Insert the VBA Code to Create the Color Function

  • Enter the following code 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

Inserting VBA Code to Create ColorFunction in Excel


Step 3 – Save the Workbook as an Excel Macro-Enabled Workbook

  • Go to the File tab.

Opening File Tab from Excel Ribbon

  • Select Save As.
  • Choose Excel Macro-enabled Workbook (*xlsm) and click Save.

Saving File as Excel Macro-Enabled Workbook


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)

Using ColorFunction to Count Green-Colored Cells

Here, 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.

Output of Using ColorFunction to Count Green-Colored Cells

  • You can also count the orange cells.

Count of Orange Colored Cells with ColorFunction


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)

Applying ColorFunction to Sum Green-Colored Cells

Here, 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.

Output of Applying ColorFunction to Sum Green-Colored Cells

  • You can follow the same steps for the orange cells.

ColorFunction Excel


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.

Applying Filter Tool from Data Tab

  • Click the downward arrow beside the Order Quantity (Colored column) > go to Filter by Color > select green.

Selecting Color to Filter

  • You will only see light green colored data in this column.
  • To count, use the following formula.
=SUBTOTAL(2,E5:E11)

Inserting SUBTOTAL Formula to Count Colored Cells

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.

Output of Using SUBTOTAL Formula to Count Colored Cells

  • Enter the following formula to see the count of the colored cells.
=SUBTOTAL(9,E5:E11)

Applying SUBTOTAL to Sum Green-Colored Cells

Here, 9 indicates that the cell will be added and E5:E11 is the data range.
  • Press Enter, to see the sum of green cells.

Final Output of Using SUBTOTAL Function for Colored 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.

Opening Define Name Window from Formulas Tab

  • In the New Name box, enter a name (Color).
  • Enter the formula in Refers to and click OK.
=GET.CELL(38,GET.CELL!$E5)

Inserting GET.CELL Function New Name Window

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.

Typing Formula Name to Get Color Code

  • Press Enter and use the AutoFill  to get the color codes of all cells in Column F.

Getting Color Codes with GET.CELL Function

  • Enter the following formula to get the count of green cells:
=COUNTIF($F$5:$F$11,50)

Applying COUNTIF Function to Count Green-Colored Cells

Here, $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.

Output of Counting 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)

Applying SUM Function to Calculate Sum of Green-Colored Cells

Here, 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.

Sum of Green-Colored Cells

  • Follow the same steps to get the count and sum of orange-colored cells.

Sum of Orange-Colored Cells with.CELL Function

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

4 Comments
  1. 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.

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo