How to run 2 macros when worksheet is updated [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!