worksheet_calculate to count times specific cell calculated

[tp_ryhansen]

I am trying to get my excel spreadsheet to count the number of times another cell changes as a result of a calculation on another worksheet in my spreadsheet. Make sense? So I have it so that it currently counts successfully but it is counting for any calculation made and I want the counter to go up only when a specific cell is calculated.

Here is the formula I have so far:

Private Sub Worksheet_Calculate()
With Me.Range("B2:B3")
If Me.Range("B2") Then
Me.Range("G2").Value = ((Me.Range("G2").Value) + 1)
ElseIf Me.Range("B3") Then
Me.Range("G3").Value = ((Me.Range("g3").Value) + 1)
Else
End If
End With
End Sub

So I want the value of G2 to go up only when B2 changes as the result of calculation. And I want G3 to go up only when B3 changes as the result of a calculation. With the code as it is now G2 goes up when B2 AND B3 change. G3 won't go up by one at all. What is wrong?

 

[Von Pookie]

You're using an ElseIf statement, which means only one of those if statements will run at any time--not both.

Just use 2 separate statements. This seems to work for me:

Private Sub Worksheet_Calculate()

If Range("B2") Then Range("G2").Value = Range("G2").Value + 1
If Range("B3") Then Range("G3").Value = Range("G3").Value + 1

End Sub