Add cells based on background colour - Glitch

Hi All,

I recently created a macro (with a little help from some forums) that was designed to sum a row of cells IF the cell formatting used a particular colour in the background. It worked a treat, and this is what it looked like

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

It does what I want it to do, except when I use 'undo'. For instance, I change one of the cells to my special background colour (blue) - the sum changes as planned, but then if I click undo, the background changes back to the old colour (grey) but instead of the sum changing again, it keeps the new total. i.e the sum of blue cells and one grey cell. I want it to only show me the totals of my blue cells.

Can anyone suggest how I ought to tweak the above code so that it will always show the correct total?

Post withdrawn

you will find the issue with UDF is it doesnt react to changes made to your spreadsheet without "re-running" the code
i assume you know you can update by going into the cell with the formula press f2 and enter which is why you want something more "dynamic"

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult As Long
    
    Application.Volatile True
        
    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

adding in application.volatile will somewhat remedy this but be warned, it does degrade performance due to the constant running of the code..this becomes important if the size of your spreadsheet is large or the number of times you use this UDF

if its only something small or you dont use this UDF often you probably wont notice

ps even with this, you need to force update (F9) in order to update formula

not a problem
glad to help

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

try using double instead of long