With the help of these user-defined functions, you can turn color into meaningful information. This walk-through will show you how.
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.
Before we begin, be sure to save the workbook you're using as a macro-enabled file as follows:
- Click the File tab and choose Save As in the left pane.
- Choose Excel Macro-Enabled Workbook (*.xlsm) from the second dropdown.
- 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.
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: green if the value is $1,000 or greater and red if the value is $200 or less. Now, granted, you'd probably use conditional formatting in this situation, so please forgive the contrived example.
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.
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:
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:
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.
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:
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.
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:
This UDF uses the syntax
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.
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
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.
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.)
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.
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 email@example.com.
- Five ways to take advantage of Excel list features (TechRepublic)
- Microsoft Office tip: The new AutoSave may not be as useful as you think (ZDNet)
- Microsoft Office Certification Training Bundle (TechRepublic Academy)
- Office Q&A: Two easy ways to repeat text in a Word document (TechRepublic)