Count or Sum of colored cells

[RAMU]

Dear Friends,

Refer to my title I have been using UDF from the following link:
http://www.ozgrid.com/VBA/Sum.htm

But excel can't automatically change the value if I modify any cell color.

Is there any updated code using which values will be changed automatically ?

Any help will be highly appreciated.


Regards
RAMU

 

[Kenneth Hobson]

Use the Sheet's Selection Event to do a Calculate.

 

[RAMU]

Dear Mr. Hobson,

Thnx for your help but can't get you. Can you pls explain the process?

Regards
RAMU

 

[Kenneth Hobson]

The selection event fires at the beginning so the best bet is to make the function volatile. This means that when any recalculation is done, it will fire.

I will list the selection event after the example. Right click the Sheet's tab, View > Code, and paste. To use it after you change the cell rColor, reselect it to fire it. You can add some other ranges to trigger the code in the Intersect().

Function SumColor(rColor As Range, rSumRange As Range)

Application.Volatile
''''''''''''''''''''''''''''''''''''''

'Written by Ozgrid Business Applications

'www.ozgrid.com



'Sums cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''

  Dim rCell As Range

  Dim iCol As Integer

  Dim vResult



  iCol = rColor.Interior.ColorIndex



    For Each rCell In rSumRange

      If rCell.Interior.ColorIndex = iCol Then

        vResult = WorksheetFunction.Sum(rCell) + vResult

      End If

    Next rCell



  SumColor = vResult

End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim iRange As Range
  Set iRange = Intersect(Target, Range("B1"))
  If iRange Is Nothing Then Exit Sub
  Application.Calculate
End Sub