I am a total newbie in this and I would really appreaciate your help. I am trying to make macro for counting of cells of same colour in a sheet. I googled and modified and tested and this 2 macros work:
Function CountColor(InRange As Range, ColorIndex As Long) As Long
Dim R As Range
Dim N As Long
Dim CI As Long
CI = ColorIndex
For Each R In InRange.Cells
If R.Interior.ColorIndex = CI Then
N = N + 1
CountColor = N
whatcolorindex = rcell.Interior.ColorIndex
This works perfectly for what I need.
But when the sheet is changed, I need to update values given by these macros manually, even F9 does not work. I tried to use:
Private Sub Worksheet_Change(ByVal Target As Range)
but is seems that Private sub can only make one macro at time, am I right or absolulety wrong? How to make the 2 macros run every time the worksheet changes (colour or value)?
Thank you in advance for your ideas and comments!
However your macro needs parameters. I dont see you passing any thing when you call Worksheet_Change.
Other issue is that your both are functions. Function return value. So in the change event had you even passed the parameter to your two functions, it would not matter as the function values are not used.
you can try to add
Application.volatile to both functions. This would result if there is any change in any formula, this function would be recalcuated too.