Sum up colored cells automatically

[Marq]

using excel 2013

I found this code and this formula on the net. But it does not calculate automatically. What I am trying to do, as I color cells in a row one specific color i'd like the number of colored cells show up in cell D4.

Here is the code and formula i found but as i said, it is automatically counting.

the code:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

the formula:

i tired both of these:

=colorfunction(A1,A1:D7,FALSE)
=colorfunction(A1,A1:D7,TRUE)

 

[hiker95]

Marq,

Here are two functions for you to consider (instructions within the functions), as long as the cell colors are not the result of conditional formatting.

Function SumByColor(CellColor As Range, SumRange As Range)
' SumByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 07/25/2011 by hiker95
' =SumByColor(J1,A1:G1)
' =SumByColor(cell_address_where_a_specific_color_is, range_to_Sum)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + myCell.Value
  End If
Next myCell
SumByColor = myTotal
End Function


Function CountByColor(CellColor As Range, SumRange As Range)
' CountByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 07/25/2011 by hiker95
' =CountByColor(J1,A1:G1)
' =CountByColor(cell_address_where_a_specific_color_is, range_to_Count)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + 1
  End If
Next myCell
CountByColor = myTotal
End Function