Excel counting by colour VBA not updating with changes?

Question

I have some VBA in Excel that counts if a range of cells contains a colour from a reference cell, which works well. But changing the colour of any cell in the range does not update the total counted.

Ideally, I want the code to update the number of coloured cells that match the reference, and when the cell colours are changed, to update the sum counted.

I have tried adding an Else statement in the If statement so that if a cell is 'blank' or had its colour 'cleared' using the paint pot button for filling cells, it would deduct a number, but this failed to work.

Sub Colourif()
End Sub

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

If I change a cell in the range fill using the paint pot icon to say 'clear' it does not change the count of cells filled with the reference colour.

I would be extremely grateful for some advice on how to ensure this code automatically updates the sum when cell fills are changed from the reference colour.

Do you expect the change to happen when you change the cell or when you change the cell and re-run the code? — user11509084
What is the point of Sub ColorIf? It has no executable statements. In any event, changing a cell color does not trigger a worksheet calculation, which might be your issue. — John Coleman
@ Gareth - I do expect a change when the cell changes without having to re-run the code, like a standard Excel formulae I suppose — Mark Hutchins
@ John, My mistake, I put this in by mistake, or at least forgot to remove this sub. So I need to trigger a worksheet calculation? I'll look into this, thank you — Mark Hutchins

John Coleman John Coleman · Accepted Answer · 2019-05-29T12:14:09

Unfortunately, it doesn't seem that a format change triggers any of the standard Excel event handlers. Something which comes close would be to target the Worksheet's Selection Change event. In the code module for the worksheet put:

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

Then change the definition of CountColorIf so that it begins:

Function CountColorIf(rSample As Range, rArea As Range) As Long
    Application.Volatile

Then, if a color of a cell is changed, the count is updated -- as soon as you move to a different cell.

Another idea would be to use Application.OnTime to run a sub which triggers a recount every few seconds, but this would still not lead to instant updates as soon as you change a color.

On Edit Since there is no FormatChange event that you can target, using SelectionChange as a proxy for it is not ideal. One problem, that you noted in the comments, is that this will fire in the middle of a copy-paste act, in which case the resulting worksheet calculation will get Excel out of copy-paste mode. One fix for that particular problem is to change the code to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = 0 Then Me.Calculate
End Sub

If this code is for your own personal use, a simple solution is to keep Application.Volatile but get rid of the event handler entirely, getting into the habit of hitting F9 when you change the color of a cell. That would limit the recalculations to when you want them, at the cost of required action on your part.

Excel counting by colour VBA not updating with changes?

1 Answers