我有这样一个需求，假设表格如下：
A B C D
1 1 2 5 8
2 3 4 6 9
假设A1和C2单元格的背景是红色，我想统计a1:d2区域中背景为红色的单元格内值之和，在例子中，A1+C2 = 1+6=7
不知道有没有办法写成公式或者函数，如何在excel中调用这个公式和函数？
------解决方案--------------------------------------------------------
Mark
------解决方案--------------------------------------------------------
Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = ActiveSheet.Range( "a1:d14 ")
MsgBox SumByColor(rng, 3)
'MsgBox InStr(1, "asdf ", "a ")
End Sub
Private Function SumByColor(Area As Range, color As Integer)
Dim sum As Integer
sum = 0
Rem Area --> the range which should be seached, for example, a1:d2
Rem color --> the color no of the bgcolor, for example, 40
sum = 0
Dim rowCol() As String
Dim rowStart As Integer
Dim rowEnd As Integer
Dim colStart As String
Dim colEnd As String
rowCol = Split(Area.Address, ": ")
rowCol(0) = Replace(rowCol(0), "$ ", " ")
rowCol(1) = Replace(rowCol(1), "$ ", " ")
If InStr(1, "123456789 ", Mid(rowCol(0), 2, 1)) > 0 Then 'Column 's Model A , Z
rowStart = Mid(rowCol(0), 2, Len(rowCol(0)))
colStart = Mid(rowCol(0), 1, 1)
Else 'Column 's Model AA , AZ
rowStart = Mid(rowCol(0), 3, Len(rowCol(0)))
colStart = Mid(rowCol(0), 1, 2)
End If
If InStr(1, "123456789 ", Mid(rowCol(1), 2, 1)) > 0 Then
rowEnd = Mid(rowCol(1), 2, Len(rowCol(1)))
colEnd = Mid(rowCol(1), 1, 1)
Else
rowEnd = Mid(rowCol(1), 3, Len(rowCol(1)))
colEnd = Mid(rowCol(1), 1, 2)
End If
Dim param As String
Dim i As Integer
Dim intStart As Integer
Dim intEnd As Integer
param = "ABCDEFGHIJKLMNOPQRSTUVWXYZ "
'get Min Column No
If Len(colStart) = 1 Then intStart = InStr(1, param, colStart)
If Len(colStart) = 2 Then intStart = _
InStr(1, param, Mid(colStart, 1, 1)) * 26 + _
InStr(1, param, Mid(colStart, 2, 1))
'get Max Column No
If Len(colEnd) = 1 Then intEnd = InStr(1, param, colEnd)
If Len(colEnd) = 2 Then intEnd = _
InStr(1, param, Mid(colEnd, 1, 1)) * 26 + _
InStr(1, param, Mid(colEnd, 2, 1))
For i = intStart To intEnd 'Loop Column
'Get Column Name
Dim col As String
If i <= 26 Then
col = Mid(param, i, 1)
ElseIf i Mod 26 = 0 Then
col = Mid(param, i \ 26 - 1, 1) & "Z "
ElseIf i Mod 26 <> 0 Then
col = Mid(param, i \ 26, 1) & Mid(param, i Mod 26, 1)
End If
Dim j As Integer
For j = rowStart To rowEnd 'Loop Row
If ActiveSheet.Range(col & j).Interior.ColorIndex = color Then
sum = sum + ActiveSheet.Range(col & j).Value
End If
Next j
Next i
SumByColor = sum
End Function