VBA code to update displayed result

[martinsimart]

Hi all

I am new to VBA/Excel so I apologise in advance for my ignorance

I am trying to insert code that will count number of cells with a fill colour, I found the code below which works once but if you alter the number of cells with fill colour it does not automatically update the count displayed in the designated cell.
I also added: =colorfunction(G1,A1:B10,FALSE) in the cell I want the count displayed
I have filled G1 with yellow to use as reference cell

what i would like is this: when any cell in the range A2:B300 has a yellow fill then the count of such cells is displayed in G2

Any tips would be appreciated

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

 

[dmt32]

Hi
welcome to forum

try this update to your code & see if does what you want

Function ColorFunction(ByVal rColor As Range, ByVal rRange As Range, Optional SUM As Boolean) As Long
    Dim rCell As Range
    Dim lCol As Long, vResult as Long
    
'A volatile function recalculates whenever
'calculation occurs in any cell on the worksheet
    Application.Volatile
    
    lCol = rColor.Interior.ColorIndex
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = IIf(SUM, WorksheetFunction.SUM(rCell, vResult), 1 + vResult)
        End If
    Next rCell


    ColorFunction = vResult
End Function

Dave