How to detect cell content modifications?

[yajiv.vijay]

I want to run a macro once a particular cell is modified. How to
detect that? Is there any way to do that other than selectionchange?
Selection change doesn't give the previous value of the modified cell
I guess.

 

[Gary''s Student]

You need two macros:

I am using cell Z100 as an example

1. first set up a value in Z100
2. enter and run the following macro:

Public v As Variant
Sub sistence()
v = Range("Z100").Value
End Sub

3. finally enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("Z100")
If Intersect(r, t) Is Nothing Then Exit Sub
MsgBox (v & Chr(10) & t.Value)
v = t.Value
End Sub

each time Z100 is changed, both the old and new values are displayed.

 

[Rick Rothstein \(MVP - VB\)]

Instead of calling a separate macro, I think you can automate the entire
process like this...

Public V As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("Z100")) Is Nothing Then Exit Sub
' Your code goes here... MsgBox for example purposes only
MsgBox "Old Value: " & V & Chr(10) & "New Value: " & Target.Value
' Make this the last statement in this event procedure
V = Target.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Z100")) Is Nothing Then V = Target.Value
End Sub

Rick