Allow volatile expression in VBA function

[TLO2015]

Hello,

I have a formula that checks the bgcolor of a reference cell (rColor), then selects all cells in the defined range (rMatrix) with corresponding color in order to calculate the sum or count the number of cells.

Problem is that I want to enter a specific formula that determines the needed range in rMatrix.
However I receive the message that the value for rMatrix is volatile.
example: rMatrix should be: cell("contents";A1) - where as the cell A1 contains the cell(address) values for the desired range.

my code for sum by color:

Function SomKleur(rColor As Range, rMatrix As Range, Optional SUM As Boolean)
    Application.Volatile
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult

    lCol = rColor.Interior.ColorIndex

    If SUM = True Then
        For Each rCell In rMatrix
            If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else

        For Each rCell In rMatrix
            If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If

   SmColor = vResult

End Function

Thanks for your help/feedback!
T.

 

[AlphaFrog]

Try this to convert the address in the A1 cell into a range...

For Each rCell In Range(rMatrix.Value)

 

[TLO2015]

Thanks Alpha!

This was exactly what I needed!