Sum fill colored items

[howard]

I have items in Col D which is colored in yellow using a fill color

I use a UDF to sum these items. However, when I add color additional values using the fill color the values are not updated. I have to edit the function and enter and then enter in order for it to update



I have added code Application.CalculateFull to the end of my code, but this still does not update when I add a fill color using yellow

 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

Application.CalculateFull



End Function

It would be appreciated if someone could assist me

 

[Yongle]

Try this

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
[COLOR=#b22222]Application.Volatile[/COLOR]
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

 

[footoo]

Put at the start of the function : Application.Volatile
Then the result will update when the sheet recalculates (can force calculation by pressing F9)

In addition, can add this event procedure so that it updates on a change in selection :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

 

[howard]

Thanks for your help guys. This is much appreciated