SUM by color: Calculate when color changed

posted Nov 4, 2013, 2:33 PM by Krisztina Szabó   [ updated Nov 7, 2013, 2:35 PM ]
Igor: Are you sure you want to go through with this?

by The FrankensTeam

A frequently asked question in Excel forums is How to filter / sum data by color. In Excel 2007 or higher versions the autofilter and sort features are capable to work with colors, but there is no built-in formula which could result the color (or other format property) of a cell or range. Obviously it is possible to write a UDF to solve this challenge. The only problem will be that format change does not trigger calculation.

We would like to share two workarounds to bridge this problem and trigger calculation when cell color is changed.

Solution 1

The first solution was published by Igo...r few years ago (here you can read the original article in italian) It still works in the newest versions of Excel too, so here is the english version.
The base idea is to use the OnUpdate event of the Commandbars class: this is fired when Excel updates the toolbar’s state. This happens not only when we press a button on a toolbar, but every time when text or cell selection changed. It means a lot of events, so we must pay close attention for not overloading the event handling procedure.
Let’s see how we can use it in practice.
First of all, we will need a class module (in the example it is named Classe1) with this code in it:

Public WithEvents cX As CommandBars

Private Sub cX_OnUpdate()
  Static s As String
  Static l As Long
  If s = ActiveCell.Address(, , , True) Then
    If l <> ActiveCell.Interior.ColorIndex Then
    End If
  End If
  s = ActiveCell.Address(, , , True)
  l = ActiveCell.Interior.ColorIndex
End Sub

And a normal module where we instantiate the above defined Classe1 class:

Dim cf As New Classe1

Sub Auto_Open()
    Set cf.cX = Application.CommandBars
End Sub

Sub Auto_Close()
    Set cf.cX = Nothing
End Sub

Now, we can add the UDF to read the colorindex of a cell or range:

Public Function Colore(Optional myrng As Excel.Range)
Dim r As Long
Dim c As Long
Dim Myarr()
 If myrng Is Nothing Then
    Set myrng = Application.ThisCell
 End If
 With myrng
  If .Count > 1 Then
    ReDim Myarr(1 To .Rows.Count, 1 To .Columns.Count)
     For c = 1 To .Columns.Count
       For r = 1 To .Rows.Count
         Myarr(r, c) = myrng(r, c).Interior.ColorIndex
   Colore = Myarr
   Colore = .Interior.ColorIndex
  End If
 End With
End Function

How does it work?

There are two IF statements within the OnUpdate event of the Commandbars class. Using two static variables we test if the color of the active cell was changed or not. Calculate method will only called if the color changed. This way there will be no unnecessary calculations, while the OnUpdate event is fired frequently.
The UDF formula is independent of this technique, but there is an important row in the code: Application.Volatile. This makes the UDF to be a volatile formula, so it will always be re-calculated when the sheet is calculated. It is necessary, because color change does not affect any precedents of the formula - so Excel will not see any reason to re-calculate it (in other words: the cell is not dirty).
You can download the original file with the VBA codes and example usage of the formula.

Solution 2

Disclaimer: We publish this solution because we are always interested in pushing the boundaries of “what's possible” in Excel. This solution is interesting, but it may not mean suitable for practical use.

The solution uses a special method to evaluate an UDF. You can read more about it here.
Let’s see how to build it step by step in Excel 2010 (we tested this step-by-step guide in Excel 2010, unfortunately in Excel 2013 we were not able to reproduce the model, however the solution works in that version too.)
You can use exactly the same UDF as in Solution 1, so copy it to a module in VBA.

Then create one more UDF - this one is the “illegal” UDF:
Function MyCalc()
    MyCalc = 0
'    ActiveSheet.Range("a1").Formula = ActiveSheet.Range("a1").Formula
End Function

As you see the “illegal” row is commented out not to cause problems while we work on the rest of the model.
Create a name:
rnd =IFERROR(EVALUATE(MyCalc()),0)

And insert a line chart, add a series using the name rnd as series value:

Now you can uncomment the illegal row in MyCalc UDF code - and you are finished. If you write a volatile formula, for example =rand() to a cell, you will see it will immediately be recalculated if you change the color of a cell. Also, all the volatile UDFs will be recalculated - so the Colore() UDF too.
You can download a very simple example file.