Excel VBA : recalculate in UDF sum by highlight color

Question

我搜索并发现了几个类似的问题，但未能成功找到解决方案。我希望我可以使用数组公式按颜色求和，但这不可能出现。相反，我正在冒险使用这个 vba 解决方案，但它不起作用。
我有以下书面和实现:

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

我也用 cell.Value 试过这个:

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

应用:
老板(低级 excel 用户)使用颜色来突出显示不同人的工作。这些作业是使用分钟分配的。
例如，分配给 Jonny 的工作以绿色突出显示，而分配给 Katie 的工作以蓝色突出显示。
他希望分配作业 A、B 和 C，在作业时间单元格 100、120 和 180 中以分钟表示。为此，他将每个单元格突出显示为绿色。他希望将 400 分钟的工作时间分配给 green = Jonny。
现实世界，发生了什么:

有时boss会删除单元格中的值，但不删除高亮，导致高亮的单元格为空。解决方案是输入 0 但他忘记了或只是不想要这个。他忘记了。 :/

有时他会更改单元格突出显示颜色，在本例中，从蓝色变为绿色，以分配给不同的员工。例如，他可以从作业 A 中移除蓝色突出显示(现在没有颜色)，并将作业 B 中的突出显示更改为绿色。在这种情况下，他想要一个结果:Jonny 180(只有工作 C 仍然是绿色的)； Katie 120(只有工作 B 是蓝色的)。

具体求助:

当他输入新值或更改颜色时，UDF 不会重新计算。如果我在包含 UDF 的单元格上按 Enter 键，它确实有效。实现的这些 sumcolor 总数太多，无法在每个总数上手动输入。我知道我需要编写一个触发器来重新计算 UDF。链接的其他答案中引用的解决方案将不起作用，因为我在许多工作表的许多单元格中调用 UDF。我需要在工作簿的 11 张纸上触发重新计算此 UDF 的每个实例。我希望 UDF 在输入任何工作簿单元格时重新计算，或者在更改单元格时更好。这是一个非常简单的工作簿。我不相信每次进行批发重新计算的成本都很高。

分开来说，也是一个问题:
3) 如果单元格为空或有文本条目，但也突出显示 myColor，他会收到 #VALUE 错误。我需要忽略 UDF 中的空值和文本输入。
根据这个主题的问题数量，微软应该添加一个功能
=sumcolor(cellcolormatch,sumrange) 公式……但我离题了……

Answer 1

尝试这个:

' 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

您可以使用 F9 手动重新计算整个工作簿，也可以使用 SelectionChange 事件自动计算

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