Worksheet Calculate Sub (Monitoring Multiple Events)

[indi visual]

I am trying to produce a Worksheet_Calculate event that monitors multiple events.


I have the following cells Z38:Z63 which contain the following formulas (they all reference Z1):

[pre]<br />
=COUNTIF($Z$1,"0&#34<br />
=COUNTIF($Z$1,"1&#34<br />
=COUNTIF($Z$1,"2&#34<br />
=COUNTIF($Z$1,"3&#34<br />
=COUNTIF($Z$1,"4&#34<br />
=COUNTIF($Z$1,"5&#34<br />
=COUNTIF($Z$1,"6&#34<br />
=COUNTIF($Z$1,"7&#34<br />
=COUNTIF($Z$1,"8&#34<br />
=COUNTIF($Z$1,"9&#34<br />
=COUNTIF($Z$1,"10&#34<br />
=COUNTIF($Z$1,"11&#34<br />
=COUNTIF($Z$1,"12&#34<br />
=COUNTIF($Z$1,"13&#34<br />
=COUNTIF($Z$1,"14&#34<br />
=COUNTIF($Z$1,"15&#34<br />
=COUNTIF($Z$1,"16&#34<br />
=COUNTIF($Z$1,"17&#34<br />
=COUNTIF($Z$1,"18&#34<br />
=COUNTIF($Z$1,"19&#34<br />
=COUNTIF($Z$1,"20&#34<br />
=COUNTIF($Z$1,"21&#34<br />
=COUNTIF($Z$1,"22&#34<br />
=COUNTIF($Z$1,"23&#34<br />
=COUNTIF($Z$1,"24&#34<br />
=COUNTIF($Z$1,"25&#34<br />



At any given time any one of these cells (Z38:Z63) will contain a '1' and the rest '0'


Although I got this to work, it only fires off once (and only for Z38), and then it no longer works afterward.


I need the code below accordingly with something along the lines of... If


if 'Z38' = "1", then run macro

if not then do nothing and run next,

if 'z39' = "1", then run macro

if not then do nothing and run next,

if 'z40' = "1", then run macro

if not then do nothing and run next,....etc so on and so forth


<br />
Private Sub Worksheet_Calculate()<br />
If Range("Z38&#34 = 1 Then<br />
Application.EnableEvents = False<br />
'My Code<br />
End If<br />
End Sub<br />
[/pre]


How do I code this worksheet_calculate sub correctly?

 

[xld]

[pre]<br />
Private Sub Worksheet_Calculate()<br />
Dim cell As Range</p>
<p> Application.EnableEvents = False</p>
<p> For Each cell In Range("Z38:Z63&#34</p>
<p> If cell.Value2 = 1 Then</p>
<p> 'My Code<br />
End If<br />
Next cell</p>
<p> Application.EnableEvents = True<br />
End Sub<br />
[/pre]

 

[xld]

BTW, why are you using COUNTIF. I would use


=--($Z$1=1)


etc