how to add excel calc to this function

[rcr1991]

A memer turned me on to this fxn, which works perfectly to sum values in cells with a certain internal color.

My problem is I cannot figure out how to use the stdev (standard deviation) function on the same range that is used for the sum. Suggestions?

For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
(Source: http://www.cpearson.com/excel/colors.htm)

 

[Andrew Poulsom]

Try:

Function SDByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Double
    Dim Cell As Range
    Dim OK As Boolean
    Dim i As Long
    Dim Rng As Range
    Application.Volatile True
    i = 1
    For Each Cell In InRange.Cells
        If OfText = True Then
            OK = (Cell.Font.ColorIndex = WhatColorIndex)
        Else
            OK = (Cell.Interior.ColorIndex = WhatColorIndex)
        End If
        If OK And IsNumeric(Cell.Value) Then
            If i = 1 Then
                Set Rng = Cell
            Else
                Set Rng = Union(Rng, Cell)
            End If
            i = i + 1
        End If
    Next Cell
    If Not Rng Is Nothing Then
        SDByColor = WorksheetFunction.StDev(Rng)
    End If
End Function