Macro function only runs once

Hi there,

So i've got a function in excel that basically adds up how many yellow cells there are the row.

Function CountColorIf(rArea As Range) As Long
    
        Dim rAreaCell As Range
        Dim lCounter As Long
   
    
        For Each rAreaCell In rArea
    
            If rAreaCell.Interior.ColorIndex = 6 Then
    
                lCounter = lCounter + 1
    
            End If
    
        Next rAreaCell
    
        CountColorIf = lCounter
    End function

The code works, however not immediately... So, for instance say I make two of the cells yellow, I then have to click backinto the cell in which this function is placed and press enter for the correct number to appear.

Can anyone show me how to make it so it runs any time the worksheet is changed?

I did findsomething about Worksheet.event (sorry something like that) but from my understanding that only works with a sub and not a function.

Any help would be really appretiated!

Cheers

Sam