The Idea
The way we want our function to work requires we have cells with fill colors that represent additional information. The cell color may represent things like “goal achieved” or “target not met”, etc. Cell colors can mean literally anything.
Our formula will be named SUMCOLOR and contain two arguments. The syntax is as follows:
SUMCOLOR(MatchColor, SumRange)
- MatchColor – is the address of the cell that contains the color for which we are searching. In the above example, this is cell F9.
- SumRange – is the range of cells containing values (colored or uncolored) we wish to sum. In the above example, the range is cells $A$9 through $C$16. (NOTE: This will need to be defined as an absolute reference so the SumRange remains the same for each searched color.)
If we were to write our formula in cell G9, the formula would look like this:
=SUMCOLOR(F9,$A$9:$C$16)When we press ENTER we should to see the number 40 (the only cell with a yellow fill color.)
If we pull the formula down to cells G10 and G11 we will see the calculations for Red cells and Green cells.
Setting Up the Function in VBA
Open the VB Editor
The first step is to launch the Visual Basic Editor by pressing ALT-F11 on the keyboard.
ALT-F11The Visual Basic Editor (from now on we’ll just refer to it as the VB Editor) will appear and resemble something like the following window.
Insert a Module Sheet
We need to insert a module sheet into the workbook to hold our code. One way to do this is to right-click on the entry on the left labeled “ThisWorkbook” and select Insert then Module.
We now have a new module sheet to hold our function procedure code.
Time to Write Some Code
The function code begins with the keyword “function”. This defines the code as a “function” procedure as opposed to a traditional “sub” procedure.
The name of the function is the name we will use when invoking the function in our spreadsheet. We’ll use the name “SumColor”.
We said we needed two arguments: one for the cell address for the color we are looking for, and another for the range of cells we will sum if the cell color matches the first argument. These arguments will be contained within a set of parentheses.
Keep It Simple at First
If we try to manipulate too many arguments at the same time, we can easily become overwhelmed in the code.
It’s best to make part of the logic static and focus on the other part to behave in a dynamic way. Once we have one dynamic portion working properly, we’ll turn our attention to the static portion and make it dynamic.
For this first pass, we will assume the cell color is red. Our only dynamic argument in this version will be the range of cells we are scanning, looking for red cells, and then adding the values of the discovered red cells.
The variable will be named “sumRange” and it will be classified as a Range data type.
Our answer will be a number stored in a variable classified as a Double data type.
Function SumColor(sumRange As Range) As Double
End Function
Looping Through Cells
We will be examining each cell in the sumRange argument looking for the color red. We will define a variable named “cell” and classify it as a Range data type. We’ll then create a looping instruction that loops one cell at a time through the sumRange looking for the color red.
Because we are declaring the color statically as red, we need to determine the color code for our red cells.
Discovering the number associated with the color red is simple; we will use the Immediate Window.
If you don’t have the Immediate Window displayed, press CTRL-G on the keyboard.
CTRL-G
In the Excel spreadsheet, select a cell that contains the red fill color.
In the Immediate Windows type ? ActiveCell.Interior.Color and press ENTER.
? ActiveCell.Interior.Color
We see that the color code associated with our red cell is number 255. As we loop through the cells, we’ll test the cell’s interior color to see if it is set to 255.
Our looping/testing code will be as follows:
For Each cell in sumRange
If cell.Interior.Color = 255 Then
SumColor = SumColor + cell.Value
End If
Next cell
If the currently examined cell’s interior color is red (255), then add the value to the variable named SumColor and store it in the variable named SumColor.
Realizing that sounds a bit odd, if you think about it, it’s logical. Let’s look at an example:
If the starting value of SumColor is 0, and we discover a red cell with a value of 6, we will add the current value of SumColor (0) to the newly discovered value of 6. By storing the result of the addition in the SumColor variable, we are overwriting the old answer (0) with the new answer (6).
Now the current value of SumColor is 6. Continuing our scan, we discover another red cell with a value of 12. We will add the current value of SumColor (6) to the newly discovered value of 12. We will store the result of the addition (18) in the SumColor variable, overwriting the old answer (6) with the new answer (18).
We’ll repeat this process until we run out of cells in our defined SumRange.
Running Our First Test
Select cell G10 in the spreadsheet and enter the following formula.
=SumColor(A9:C16)
We see the result 77 (65+12).
Making the Color Selection Dynamic
Return to the VB Editor.
Next, we’ll add an argument to the SumColor function to hold the cell address of the example cell. We want this argument to be the first argument in the function’s arguments list.
Update the first line of the code to include the new argument. The new argument will be named “MatchColor” and it will store a cell address, so we’ll define the data type as a Range.
Function SumColor(MatchColor as Range, sumRange As Range) As Double
In order to capture and retain the color of the cell the user provides as the sample cell, we need to declare a new variable named “myColor” and we will declare it as a Long data type.
Dim myColor as Long
The way we will obtain the cell’s color is the way we tested in the Immediate Window when we tested for the current cell’s interior color.
NOTE: Ideally, the user will never select more than one cell as the color reference cell. Because there is a chance the user may select more than one cell when they use the function, we will ensure that the only cell we query for color is the first cell in the selection.
We will set the myColor variable to the first cell’s (and hopefully the ONLY cell’s) color.
myColor = MatchColor.Cells(1, 1).Interior.Color
Now we simply replace the statically assigned number 255 that represented red with the myColor variable.
If cell.Interior.Color = myColor Then
Time to Test the Updated Code
Switching back to the Excel workbook, we’ll select cell G9 and begin typing our custom function.
=SumColor(To make this easier, we’ll launch the functions dialog box so we can see the listed arguments. Click the Insert Function button located at the left of the Formula Bar to launch the dialog box.
In the Function Arguments dialog box, we’ll set our MatchColor argument to cell F9, and the SumRange argument to $A$9:$C$16. Remember to make the SumRange reference an absolute reference (dollar signs) so it won’t change when we repeat the formula down for the next test.
When we press ENTER, we see the result of 40 for the sum of the yellow cells. If we fill the formula down two rows, we see the result of 77 for the red cells and 80 for the green cells.
Looking Good, but Can It Perform?
To test the function to see if it will provide accurate answers when the data changes, we’ll select cell B13 and change the fill color to the same red used in A11 and C12.
Unfortunately, the value in cell G10 has remained at 77. The cell value failed to update when the cell color changed.
The reason for this is the function will recalculate when a cell’s value changes, not its appearance. The changing of the fill color is cosmetic in nature, not mathematical. Excel updates formulas when updating data or the underlying math.
We can force the SumColor function to recalculate if we select the cell holding the formula, click in the formula bar (or press F2), and press ENTER. This will force a recalculation for all the SumColor formulas. This can also be accomplished by pressing Ctrl-Alt-F9 on the keyboard.
We can’t expect our users to remember to manually recalculate their sheets when the data changes. Let’s create an automated sheet recalculation to occur when the cell colors change.
Applying the Final Touches
To provide the necessary updates when a cell’s appearance changes, we will create a VBA Selection Change Event to detect the changing of a cell and force the recalculation.
We need to access the code sheet for the sheet that contains the data; in this case, the “Activity” sheet. Right-click the “Activity” sheet’s tab and select View Code.
On the code sheet for the “Activity” sheet, we will create a SelectionChange Event so we can detect any movement of the cursor or alteration of the cells.
In the upper-left corner of the code window, select the dropdown list and select Worksheet.
By selecting Worksheet, the default SelectionChange event is automatically created.
We could include code that would recalculate the sheet whenever movement or change is detected on the entire sheet. This would be with the following VBA command:
ActiveSheet.Calculate
This will work, but it may be a bit overkill. To monitor over 17 billion cells for change may overwhelm the system. We could restrict the change detection to columns A through C, or even the limited array of data in cells A9 through C16.
Say “Hello” to the INTERSECT Method
So that we only manually recalculate the sheet when the cursor moves into columns A through C, we will use a VBA method named INTERSECT.
The INTERSECT method will examine the location of the cursor and determine if it falls within the defined data columns.
Because it’s more efficient to determine if we are inside the A to C range as opposed to the D through XFD range (over 16 thousand other columns), we will test to see if there is an intersection occurring in columns A through C in our defined range.
It is often easier to write code that tests if something is NOT happening than happening. This is because nothing happening is a single thing to check for, as opposed to the infinite number of things that could happen.
This may look strange, but we will check to see if nothing is happening inside the intersection. The code appears as follows:
If Intersect(Target, Range(“A:C”)) Is Nothing Then
Because we are looking for SOMETHING to be happening within the intersection area, we will invert the results with a NOT operator.
If NOT Intersect(Target, Range(“A:C”)) Is Nothing Then
If you test the function by setting the color of a cell to one being calculated, you’ll see once you click away form the cell with the update data, the functions will recalculate to display the new results.
NOTE: When you click away from the updated data cell, make certain you click to a cell within the data range; in our case, it would be somewhere in columns A through C.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
