VBA 更改受功能影响的所有单元格的背景颜色

Question

我在 VBA 中有一个函数来循环浏览一系列单元格并将它们相加，直到总值等于设定的最大值，我希望能够做的是设置 SUM 中使用的所有单元格的背景颜色。

例如：

A, B, C, D, E, F, G, H
1, 2, 3, 4, 5,  , 5, =SumToValue(A1:E1, G1)

这将返回 3 作为 1 + 2 = 3， 3 + 3 = 6，高于最大值 5。

这是代码：

Function SumToValue(area As range, max As range)
Application.Volatile
Dim total As Long
total = 0
For Each cell In area
If (WorksheetFunction.Sum(cell, total) <= max) Then
total = WorksheetFunction.Sum(cell, total)
Else
Exit For
End If
Next cell
SumToValue = total
End Function

所以我需要在计算时设置 A1 和 B1 的背景颜色。但是，如果我尝试在循环中添加cell.Interior.ColorIndex = 50，则公式中会出现Value错误。

有没有办法这样做，或者函数不能以这种方式使用？

Answer 1

您将函数用作 UDF(用户定义函数(。在 UDF 中，不允许修改工作表中的任何内容，请参阅示例 https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel

我能想到的最好的办法是使用公式向单元格添加条件规则。在您的示例中，转到单元格 A1 并使用公式定义规则：

=SUM($A1:A1) <= $A$7

，然后输入"适用于"的=A1:A5。