Search code examples
excelvbacolorssumcell

Sum cells by colors based on other colum cells

I wanna sum cells that have the same color. I know there are some VBA functions to do that. But my problem is kinda specific. I want to sum cells values from a single column, based on cells colors on another column.

I add an example and the code I used. I got the "#VALUE" error on the line where I try to access the Interior property.

Example of sheet I want

Function SumByColor(CellColor As Range, rRange As Range)
 Dim cSum As Double
 Dim ColIndex As Integer
 Dim compatedCell As Range  
 Debug.Print ("sumbycolor called")

 ColIndex = CellColor.Interior.ColorIndex

 For Each cl In rRange
  comparedCell = Worksheets("HA").Cells(cl.Row, 1)
  Debug.Print (comparedCell.Interior.ColorIndex) #nothing printed

  If comparedCell.Interior.ColorIndex = ColIndex Then
   cSum = WorksheetFunction.Sum(cl, cSum)
  End If
  Next cl

 SumByColor = cSum

End Function

Thx for your help.

Solution

  • You should dim all your variables.

      Dim cl As Range, comparedCell As Range
  For Each cl In rRange
    Set comparedCell = Worksheets("HA").Cells(cl.Row, 1)
    Debug.Print (comparedCell.Interior.ColorIndex) 'nothing printed

    If comparedCell.Interior.ColorIndex = ColIndex Then
         cSum = WorksheetFunction.Sum(cl, cSum)
    End If
  Next cl

    As comparedCell is a Range-object you have to use Set.