VBA代码 - Sumbycellscolor

Question

LastRow = Cells(Rows.Count, 12).End(xlUp).Row 
Cells(LastRow + 1, 12).Formula = "=sumcellsbycolor(K1:K" & LastRow & ",(colorindex=6))" 

在实际公式中（colorindex = 6），我们应该选择我们需要的颜色的单元格。但是因为我没有固定的细胞给参考。

所以，我怎样才能使用这种颜色索引上述公式

Answer 1

中你可以试试这个程序代码。你可以在"L1"中获得颜色和的结果。颜色选择在Range(M1:M3)。我必须在代码中更改行号码指示。代码后给出的图像显示了示例数据。 HTH

Sub SumByColorV() 

    Dim ws As Worksheet 
    Dim lastRow As Long 
    Dim result As Long 
    Dim cSum As Long 
    Dim ColIndex As Integer 
    Dim CellColor As Range 
    Dim rRange As Range 
    Set CellColor = ActiveSheet.Cells(1, 13) 'Vary row no 1 or 2 or 3 of column 13 (M) as per yr requirement 
    lastRow = ActiveSheet.Cells(Rows.Count, "K").End(xlUp).Row 
    Set rRange = ActiveSheet.Range(Cells(1, 11), Cells(lastRow, 11)) 
    ColIndex = CellColor.Interior.ColorIndex 

    For Each cl In rRange 
     If cl.Interior.ColorIndex = ColIndex Then 
     cSum = WorksheetFunction.Sum(cl, cSum) 
     End If 
    Next cl 
    result = cSum 
    ActiveSheet.Cells(1, 12).Value = result 
End Sub 

Answer 2

下面是可以传递两个参数的函数，一个单元格范围和变体，其可以是一个显色指数，对颜色的标准名称，或1个细胞范围。如果它被传递给该参数的范围内，它总结针对该范围的颜色指数：

Function IndexFromName(ByVal colorName As String) As Long: 
    Dim s As String, i As Long 
    s = LCase(colorName) 
    Select Case s 
     Case "black": i = 1 
     Case "white": i = 2 
     Case "red": i = 3 
     Case "green": i = 4 
     Case "blue": i = 5 
     Case "yellow": i = 6 
     Case "magenta": i = 7 
     Case "cyan": i = 8 
     Case Else: i = -4142 'color automatic -- perhaps should return an error 
    End Select 
    IndexFromName = i 
End Function 

Function SumIfColor(R As Range, color As Variant) As Double 
    'summ cells with a given color index 
    'which may differ from displayed color 
    'if conditional formatting is in use! 
    Dim i As Long, ci As Long, s As Double, c As Range 

    If TypeName(color) = "Range" Then 
     ci = color.Interior.ColorIndex 
    ElseIf TypeName(color) = "String" Then 
     ci = IndexFromName(color) 
    Else 
     ci = color 
    End If 
    For Each c In R.Cells 
     If c.Interior.ColorIndex = ci Then s = s + c.Value 
    Next c 
    SumIfColor = s 

End Function 

它可以直接在电子表格中使用：

在上述图像I在单元格A14中输入公式=SumIfColor($A$1:$H$10,A13)，并将其复制到nxt两列中。

当然可以扩展功能IndexFromName。我只实现了对应于vbYellow等命名常量的8种颜色。例如，您可以在标准Excel调色板中确定其他颜色的标准HTML颜色名称。

请注意，Excel中的颜色现在比单元格的颜色索引复杂得多。显示的颜色不再局限于调色板，条件格式是一个不断复杂的因素。唯一有效的用例是当唯一显示的颜色是用户使用调色板明确选择的颜色时。