Open the VB Editor
The first step is to launch the Visual Basic Editor by pressing ALT-F11 on the keyboard.
The 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
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.
In the Excel spreadsheet, select a cell that contains the red fill color.
In the Immediate Windows type ? ActiveCell.Interior.Color and press ENTER.
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
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.