Sum cells by colors based on other colum cells

Question

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.

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.

Do you use the function as UDF (so in a formula)? Have you colored the cells manually or are they colored with conditional formatting? — FunThomas, Jun 01 '23 at 11:29
Note that it is generally a bad idea to use formatting (in this case color) to transport information. Formattings should only be used to make information (that is already present in the data itself) visible. Why is that? Because Excel is pretty slow on the formatting side and you cannot use functions like `SUMIF` or `SUMIFS` which make things really easy. Instead of using color better make a column and insert a value for each color (you can then use conditional formatting to make it visible) and `SUMIF` to easily calculate. — Pᴇʜ, Jun 01 '23 at 11:45

score 1 · Accepted Answer · answered Jun 01 '23 at 11:06

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.

Sum cells by colors based on other colum cells

1 Answers1