[VBA] count cells with color and auto refresh worksheet

[DonAndress]

Hello.

What I'm trying to achieve today is to count cells with a certain color and then make excel refresh the cell with such sum automatically.

Below I have a code for counting cells with color and it works fine:

Function CountColorIf(rSample As Range, rArea As Range) As Long    Dim rAreaCell As Range
    Dim lMatchColor As Long
    Dim lCounter As Long


    lMatchColor = rSample.Interior.Color
    For Each rAreaCell In rArea
        If rAreaCell.Interior.Color = lMatchColor Then
            lCounter = lCounter + 1
        End If
    Next rAreaCell
    CountColorIf = lCounter
End Function

as a formula I put

=countcolorif($K$1,$A$5:$BC$35)

where K1 contains desired color.

But how do I force Excel to refresh this sum automatically every time I add/remove color to any cell within specified range?
Is it even possible?
Any code inside of ThisWorkbook or Sheet1 area of the VBA Project window?

 

[jmacleary]

This will (almost) do what you want. Place this code in the worksheet code area (right-click on the sheet name and click View Code. It doesnt quite work instantly if you change the colur of any cells, but as soon as you select a different cell or range, it will recalculate.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CalculateFull
End Sub

 

[DonAndress]

Yes, this almost works as desired
As you said: it awaits any action after color change, meaning it can be even simple click on any other cell.

Is it possible to force it to refresh instantly?

 

[jmacleary]

Not as far as I know - maybe someone else can show a way!

 

[DonAndress]

Ok I understand, thank you.

Anyway, could you please help me understand below code, step by step?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

I'm not familiar with this structure and can't figure out what each part of it does.
Especially when Target is not used anywhere...

 

[jmacleary]

Hi there - of course. This code is what is called an event handler. There are several different worksheet events you can watch for (in the vb editor click on the down box next to the selectionchange box to see the list) - and you can write code that runs when that event happens. In this case, the event handler is watching for the focus in your worksheet to switch from one area to another - i.e. you make a 'selection change'. The reason the target is there is twofold - first, you will get a compilation error if you take it out, but secondly it contains a reference to the range you have just selected. This would let you have different code for different selected areas. These event handlers sit in the background and come to life when the event takes place. So, in your case, each time you select a different cell, the application.CalculateFull command is run, which forces your counters to reset.

 

[DonAndress]

Ok, I get it.
Thanks a lot for your help!