按单元格填充颜色或字体颜色统计数据的自定义函数-526互联

参考网络代码，自己写了二个通用的自定义函数，用于统计不同颜色的单元格数值或个数。

 1 Function SumColor(rng As Range, cellColor As Range, N As Byte) As Double
 2     
 3     '输入=SumColor(A1:A10, A1, 0)，其中A1:A10是统计的范围，A1是统计的颜色所在的单元格，0表示按照背景颜色统计,1表示按字体颜色统计
 4     
 5     Dim cell As Range
 6     Dim Sum As Double
 7     
 8     Application.Volatile True   '单元格发生改变，那么函数会重新计算
 9     
10     Sum = 0
11     If N = 0 Then
12         For Each cell In rng
13             If cell.Interior.ColorIndex = cellColor.Interior.ColorIndex Then
14                 Sum = Application.Sum(cell) + Sum
15             End If
16         Next cell
17     ElseIf N = 1 Then
18         For Each cell In rng
19             If cell.Font.ColorIndex = cellColor.Font.ColorIndex Then
20                 Sum = Application.Sum(cell) + Sum
21             End If
22         Next cell
23     Else
24         Exit Function
25     End If
26     
27     SumColor = Sum
28     
29 End Function
30 
31 Function CountColor(rng As Range, cellColor As Range, N As Byte) As Long
32     
33     '输入=SumColor(A1:A10, A1, 0)，其中A1:A10是统计的范围，A1是统计的颜色所在的单元格，0表示按照背景颜色统计,1表示按字体颜色统计
34     
35     Dim cell As Range
36     Dim Sum As Long
37     
38     Application.Volatile True   '

单元格发生改变，那么函数会重新计算

 39 40 Count = 0 41 If N = 0 Then 42 For Each cell In rng 43 If cell.Interior.ColorIndex = cellColor.Interior.ColorIndex Then 44 Count = Count + 1 45 End If 46 Next cell 47 ElseIf N = 1 Then 48 For Each cell In rng 49 If cell.Font.ColorIndex = cellColor.Font.ColorIndex Then 50 Count = Count + 1 51 End If 52 Next cell 53 Else 54 Exit Function 55 End If 56 57 CountColor = Count 58 59 End Function

颜色