Count the number of changes to a cell

[Guest]

Is there a function to keep track (count) of how many times a particular
cell, the date in the cell, has changed? Thanks!!!

R

 

[T. Valko]

It's possible and is fairly complicated but may not be practical in most
applications.

http://www.oaltd.co.uk/Excel/Default.htm

Look for Previous.zip

 

[Gord Dibben]

Event code can do this.

Enter a 0 in C1 then copy/paste this code into your worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value <> "" Then
With Target.Offset(0, 2)
.Value = .Value + 1
End With
End If
enditall:
Application.EnableEvents = False
End Sub

Right-click on the sheet tab and "View Code"

Paste into that sheet module.

Whe you change A1 C1 will add to the count each time.


Gord Dibben MS Excel MVP

 

[T. Valko]

Gord, I couldn't get this to work properly.

It only counted a change once. The counter cell went from 0 to 1 and that's
it!

You left events disabled at the end. Even turning events back on didn't make
a difference.

 

[Gord Dibben]

Works for me.

Enter 0 in C1 and add something to A1

Change A1 and number in C1 increases for me each time a change is made.

Thanks for pointing out the not turning avents back on. Damn copy and paste<g>


Gord

 

[T. Valko]

Well, I must have screwed up something when I first tried this!

It works now. Much better than the method I referenced.