Excel VBA: recalculate in UDF sum by highlight color

Question

I have searched and found several similar questions asked but have been unsuccessful finding a solution. I was hoping I could use an array formula to sum by color, but this is not possible it appears. Instead, I am venturing into this vba solution and it is not working.

I have the following written and implemented:

Option Explicit

Function SumColor(MatchColor As Range, sumRange As Range) As Double
Dim cell As Range
Dim myColor As Long

myColor = MatchColor.Cells(1, 1).Interior.Color

For Each cell In sumRange
    If cell.Interior.Color = myColor Then
        SumColor = SumColor + cell.Formula
    End If
Next cell

End Function

I also tried this with cell.Value as so:

Option Explicit

Function SumColor(MatchColor As Range, sumRange As Range) As Double
Dim cell As Range
Dim myColor As Long

myColor = MatchColor.Cells(1, 1).Interior.Color

For Each cell In sumRange
    If cell.Interior.Color = myColor Then
        SumColor = SumColor + cell.Value
    End If
Next cell

End Function

Application: Color is used by a boss (low-level excel user) to highlight jobs going to various people. Those jobs are assigned using minutes.

For example, Jobs assigned to Jonny are highlighted green while jobs assigned to Katie are highlighted blue.

He wishes to assign jobs A, B and C, represented with minutes in the job time cells of 100, 120, and 180. To do this, he highlights each of those cells green. He expects to see 400 minutes of job time assigned to green = Jonny.

Real world, what's happening:

• Sometimes boss deletes the value in the cell, but does not remove the highlight, resulting in a highlighted cell with null. The solution is to type 0 but he forgets or just does not want this. He forgets. :/
• Sometimes he changes the cell highlight color, in this example, from blue to green, to assign to a different employee. For example, he may remove the blue highlight from job A (now has no color), and change the highlight in job B to green. In this case, he desires a result: Jonny 180 (only job C is still green); Katie 120 (only job B is blue).

Specific ask for help:

1. When he enters a new value or changes the color, the UDF does not recalculate. If I hit enter on the cell containing the UDF, it does work. There are too many of these sumcolor totals implemented to manually hit enter on each one. I understand I need to program a trigger for recalculating the UDF. The solution cited in the other answer linked will not work as I am calling the UDF in many cells across many worksheets. I need to trigger a recalculate every instance of this UDF on 11 sheets in the workbook. I'd like the UDF to recalculate upon enter in any workbook cell or better upon changing cells. This is a very simple workbook. I don't believe a wholesale recalculate is expensive to conduct each time.

Separately, but also a problem:
3) If a cell is null or has a text entry, but is also highlighted myColor, he gets a #VALUE error. I need to ignore both null and text inputs in the UDF.

Based on the number of questions on this topic, Microsoft should add a function

=sumcolor(cellcolormatch,sumrange) formula... but I digress...

Answer 1

Try this:

' regular module
Option Explicit

Function SumColor(MatchColor As Range, sumRange As Range) As Double
    Application.Volatile    'recalculates with any recalculation
    Dim cell As Range, myColor As Long
    
    myColor = MatchColor(1).Interior.Color
    
    For Each cell In sumRange
        'summarize only numbers (not errors or strings) with a specified color
        If VarType(cell) = vbDouble And cell.Interior.Color = myColor Then
            SumColor = SumColor + cell
        End If
    Next cell
End Function

You can recalculate the whole workbook manually with F9 or do it automatically with SelectionChange event

' this workbook class code module
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Sh.Calculate
End Sub