Software

How to use VBA to sum Excel values by fill color

With the help of these user-defined functions, you can turn color into meaningful information. This walk-through will show you how.

wolfman57istock-131402390.jpg

Image: iStock/wolfman57

The article How to easily sum values by a cell's background color in Excel explained how to combine color and built-in filters to sum values by a cell's fill color. It's an easy way to turn colors into meaningful information. A simple filtering technique won't always be adequate though, especially if you're reusing the sum in other expressions. If you need a stand-alone sum, you can use one of two VBA user-defined functions (UDFs). One relies on a helper column; the other one doesn't. Neither requires a lot of code or specialized knowledge. On the downside, both have limitations: Neither UDF works with conditional formatting or is dynamic. However, if this is what you need, you can probably live with those limitations.

I'm using Excel 2016 (desktop) on a Windows 10 64-bit system, but both UDFs will work in older versions. However, the browser version doesn't support VBA. You can use your own data or download the demonstration .xlsm, .xls, .cls, and .bas files.

Macro-enabled file

Before we begin, be sure to save the workbook you're using as a macro-enabled file as follows:

  1. Click the File tab and choose Save As in the left pane.
  2. Choose Excel Macro-Enabled Workbook (*.xlsm) from the second dropdown.
  3. Click Save.

The downloadable demonstration file is already in this format. If you're using a menu version of Excel, this step isn't necessary. If the SUMIF() doesn't return the expected results, make sure the fill color in the header cells is the same as the one in our dataset.

SEE: 30 things you should never do in Microsoft Office (free TechRepublic PDF)

A simple UDF and helper column

If you're using direct formatting instead of a conditional formatting, you can use a simple (but limited) VBA UDF to sum values by the cell's fill color. The sample sheet in Figure A shows a few direct formats in column B: red if the value is $1,000 or greater and green if the value is $200 or less. Now, granted, you'd probably use conditional formatting in this situation, so please forgive the contrived example.

Figure A

excelsumcolor2a.jpg
The direct formatting in column B identifies the highest and lowest values.

The first step is to add the simple UDF shown in Listing A to your workbook. To do so, press Alt+F11 to open the Visual Basic Editor (VBE). From the Insert menu, choose Module. Then, enter ColorSum() manually or import the downloadable .bas file. Don't copy and paste from this web page.

Listing A

Function ColorSum(CellColor As Range)
    'Sum values by cell's background color.
    ColorSum = CellColor.Interior.ColorIndex
End Function

ColorSum() passes the current cell as a range object and returns the ColorIndex property setting. Enter the following function into D2 and copy it to the remaining cells, as shown in Figure B:

=ColorSum(B2)

Figure B

excelsumcolor2b.jpg
Call the UDF.

The UDF references the cells in column B and returns the index value for each cell's fill color: Green is 48 and red is 3. We don't care about the cells with no color assigned (-4142).

Next, enter the following SUMIF() in E2 and copy it to the remaining cells:

=SUMIF($D$2:$D$18,$D2,$B$2:$B$18)

As you can see in Figure C, the SUMIF() function returns the totals for the green and red cells: 3,600 and 602, respectively. The first range refers to the color index values returned by the UDF in column D. The reference to $D2 uses the current index value as criteria—we're looking for index values in D2:D18 that match the index value in D2. The final range reference identifies the values the function sums when the first two values match.

Figure C

excelsumcolor2c.jpg
SUMIF() returns a total for the green and red cells.

Frankly, it's a bit of a mess. Even though it works, you probably won't be happy with this arrangement. Figure D shows a more reasonable setup. Manually add the fill colors to G1 and H1; we'll use them as header cells and criteria. Then, enter the following function in G1 and copy it to H1:

=SUMIF($D$2:$D$18,ColorSum(G$1),$B$2:$B$18)

The first range refers to the index values returned by the UDF. The second argument, ColorSum(), returns the color index value for the header cell; in this case the cell is G1 and the index value is 48. When the values for the first two arguments match, this function sums the corresponding values in B2:B18. If you don't want to see the index values in column D, hide the column. Consequently, the values in column E aren't necessary.

Figure D

excelsumcolor2d.jpg
This simple matrix simplifies the summing.

SEE: Securing Windows policy (Tech Pro Research)

An improved UDF

The first UDF depends on a helper column—the color index values in column D. You can eliminate this column using the UDF shown in Listing B. Figure E shows the results of this UDF in the simple matrix, but in row 3. Enter the following function into G3 and copy it to H3:

=ColorSum2(G$1,$B$2:$B$18)

This UDF uses the syntax

=COLORSUM2(criteria,range)

where criteria references the color you're matching and range denotes the values you're summing. In a nutshell, this UDF compares the fill color of the current cell (B2:B18) to the fill color in the criteria cell (G1). When those values match, the function adds the value in the current cell to lSum. After checking all the cells in the passed range (rng), the UDF returns the final value of lSum.

Listing B

Function ColorSum2(CellColor As Range, rng As Range)
    'Sum values by cell's background color.
    Dim lSum As Long
    Dim iIndex As Integer
    Dim cclr As Variant
    iIndex = CellColor.Interior.ColorIndex
    Debug.Print iIndex
    For Each cclr In rng
        If cclr.Interior.ColorIndex = iIndex Then
            lSum = WorksheetFunction.Sum(cclr, lSum)
        End If
    Next cclr
    ColorSum2 = lSum
End Function

Figure E

excelsumcolor2e.jpg
This improved UDF eliminates the helper column.

Dynamic, sort of

Both UDFs work, but neither is dynamic. Figure F shows the result of changing the color of B3—neither UDF updates. After changing, adding, or deleting a color, you must force the file to recalculate by pressing Ctrl+Alt+F9. You could set the Volatile property to True in the UDFs, but you'd still have to press F9; that's not much better—okay, it's not any better at all.

Figure F

excelsumcolor2f.jpg
The UDF doesn't update; it's a one-time deal.

There's no easy way to make either UDF dynamic. Listing C contains an event procedure that relies on the Worksheet_SelectionChange event. When you select a new cell, this procedure forces the file to calculate when the fill color changes. Again, enter the code manually or import the downloadable .cls file. This code goes in the sheet's module, not the module you added earlier. (You can choose the sheet in the VBE's Project Explorer.)

Listing C

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Compares color after selection change to force calculaton.
    'Forces UDFs, ColorSum() and ColorSum2() to recalculate
    'when fill colors are changed.
    Static LastRange As Range
    Static LastColorIndex As String
    If LastRange.Cells.Interior.ColorIndex <> LastColorIndex Then
        Application.CalculateFull
    End If
    Set LastRange = Target
    LastColorIndex = Target.Cells.Interior.ColorIndex
End Sub

While it helps, it's not an elegant solution either. You must remember to move the selection to trigger it, so it's still problematic. I provide this last procedure to be comprehensive; it doesn't really improve the situation because either way you must remember to force the recalculation. In addition, every time you select a different cell, you trigger this procedure; it will slow things down in a busy sheet. I can't truly recommend its use, but you decide for yourself. For better or worse, I prefer the Ctrl+Alt+F9 keystroke to the worksheet event procedure. I recommend looking for a way to combine this UDF in an automated process so you can add a recalculation method, eliminating the need to remember to do so manually.

Stay tuned

Besides their non-volatile state, both UDFs have one more huge limitation—neither works with conditional formatting. In a future article, I'll provide a similar solution that works with conditional formatting. I don't care for these UDFs because they're not dynamic, but I know of no other way to get these results using VBA without creating a class module that captures format changes, and that's well beyond the scope of a short article.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read...

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox