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.