How to run 2 macros when worksheet is updated

Closed
Veronika - Sep 16, 2011 at 04:53 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 16, 2011 at 09:15 AM
Hello,
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
End If
Next R
CountColor = N
End Function

Function whatcolorindex(rcell)
whatcolorindex = rcell.Interior.ColorIndex
End Function

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)
whatcolorindex
CountColor
End Sub
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!
Veronika

Related:

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Sep 16, 2011 at 09:15 AM
No a macro can have more than one macro.

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.
0