Sumcolor in VBA gives me invalid argument + it won't load after it's saved and file is reopened

[layla]

Hello!

I'm trying to create a spreadsheet that automatically sums values in cells based on their colors.

For example:

• Red cells should be summed together.
• Green cells should be summed together.
• Blue cells should be summed together.
• Yellow cells should be summed together.

I want the sums to dynamically update. So, if I change a blue cell to red, the sum of red cells should increase, and the sum of blue cells should decrease.

I've saved the file as a Macro-Enabled Workbook, but I'm encountering these issues:

• When I try to use the formula, I get an "invalid argument" error.
• The formula disappears when I close and reopen the file."

Using the VBA:
in the sheet itself, I have this added:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A:Q")) Is Nothing Then
    ActiveSheet.Calculate
End If
End Sub

Then I added a module to that sheet that includes the following:

Option Explicit

Function SumColor(MatchColor As Range, sumRange As Range)
   Application.Volatile True
    Dim cell As Range
    Dim myColor As Double
    myColor = MatchColor.Cells(1, 1).Interior.Color
    For Each cell In sumRange
        If cell.Interior.Color = myColor Then
            SumColor = SumColor + cell.Value
        End If
    Next
End Function

NOTE: I need this to be done to all 5 sheets in this workbook

I attached the file so you guys can check.

Thank you for your help!

 

[Marc L]

Hello, according to your attachment - no issue on my side - you must​

• first delete the useless modules #2 to 5 on VBE side !
  
  
• Update this VBA function in Module1 :
  

  Function SumColor(sumRange As Range)
           Application.Volatile True
           Dim Cell As Range
      For Each Cell In sumRange
            If Cell.Interior.Color = Application.ThisCell.Interior.Color Then SumColor = SumColor + Cell.Value2
      Next
  End Function

  
  
• Apply the back color to sum to each formula cell in L4:L8.
  
  
• Apply this unique formula =SumColor(H11:H139) to each cell in L4:L8 …

Do you like it ? So thanks to click on bottom right Like !​

​