Properly call a cell formular from a macro in excel vba

Question

im trying to work out a little scheduler in excel to manage my life a little better. I use different cell colors for each event (yellow for work, red for university, etc.). I already have a macro CountColor which counts the occurence of a color in a certain range which works fine (i pretty much copy pasted it from an online solution). I now use the macro to calculate the used time into cells like this:

the cells contain =ColorCount(H5;B2:F15) whereas the interior color of the first argument determines the color to count and the seconds parameter is the range to count the color in. This macro works fine. The last number is just the sum of the above three.

I now however face the problem, that changing the interior color of a cell does not trigger the recalculation of formulars. I created a simple button (not the ActiveX one) and assigned a macro to it:

Public Sub CalcButton_onclick()
    Worksheets(1).Range("I13:I16").Calculate
End Sub

but when i click the button (i also tried to recalculate the whole sheet by using Worksheets(1).Calculate) nothing happens. Only when i, for example, change the value int the cell my times get recalculated. My button's macro is definitely executed i tested that by adding Worksheets(1).Cells(20, 20).Value = "Test" after the Calculate call and it changed the value of the given cell properly.

For the purpose of completion, i also add the code of the CountColor macro:

'counts the occurence of the interior color of rColor in rRange
Public Function ColorCount(ByRef rColor As Range, ByRef rRange As Range) As Integer
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult As Integer
    vResult = 0
    lCol = rColor.Interior.ColorIndex
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = 1 + vResult
        End If
    Next rCell
    ColorCount = vResult
End Function

Im not really sure what the problem is but i believe i may have misinterpreted the Calculate method. I only created the 2 macros above. I appriciate any help!

By the way something meta: is this a proper use of a picture in a question? I could not think of a better way to show what i want my output to look like.

Answer 1

I found the problem myself.

At first i want to thank everyone for their hints and tips. I would propably have needed them after fixing my initial problem and so i had them fixed in advance :)

My problem actually was apparently, that i wanted to declare my spare time as white color. But actually i had several cells which had "no fill color" after coloring every free cell explicitely white it now works with the button. The solution with Worksheet_Change() method in the sheet code did not work unfortunately because a color change is not evaluated as a change in the sheet. Worksheet_SelectionChange() however did the trick with updating when you click on another cell so i do not need the button anymore.