Updating a count every time a value is changed

Posted by Dan on July 11, 2001 8:33 AM

I'm trying to have a cell with a starting value of "0" that will increase every time another refrenced cell is changed. For example, Cell A1 is a TRUE/FALSE value, and I want Cell A2 to count how many times Cell A1 has changed between true and false. I've racked my brain trying to come up with ways to do this, even looking into macros and user-defined functions, and am having trouble. Does anyone have any suggestions? Your help would be greatly appreciated.

Posted by Tom Morales on July 11, 2001 11:27 AM

Right-click your worksheet tab, and select "View Code." Then insert the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
n = Range("A2").Value
If Target.Address = "$A$1" Then
n = n + 1
Range("A2").Value = n
End If

Good Luck
Tom
End Sub

Posted by Dan on July 12, 2001 8:10 AM

Thanks a billion! Is there any way of modifying that to work if cell A1 is formula that changes with another cell?
-Dan

Posted by Tom Morales on July 12, 2001 11:28 AM

Something like this might work for you:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
n = Range("$A$2").Value
'E1 is an arbitrary cell that we'll use for comparisons
oldno = Range("E1").Value
currentno = Range("A1").Value
If Not oldno = currentno Then
n = n + 1
Range("E1").Value = currentno
Range("A2").Value = n
End If
End Sub

Tom

Posted by Dan on July 13, 2001 2:14 PM

problem solved!

Thanks, it works great! I really like the way you think when it comes to solving the programming problems.