If you want to use the named range, you would select the cell you want to
keep track of, then click in the name box in the top left corner (right above
column A) and type in your range name.

You could use

If Target.Address = "$A$5" Then
instead of
If Not Intersect(Target, Range("MyRange")) Is Nothing Then

but if a row is inserted above row 5, then your cell is now actually A6.
The macro will still be hardcoded to look for cell A5, which is not a problem
if you are certain that cell will not get moved (or are okay w/changing the
code whenever it does move).




"ViestaWu" wrote:

Do I need to define the "Myrange" at advanced?

"JMB" wrote:

Right click on your sheet tab, select view code and paste this into the code
window. "MyRange" is a named range that refers to the cell you want to track
(the code will adjust to the user inserting/deleting rows/columns which cause
your cell to move around - VBA doesn't track this like XL formulae do).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("MyRange")) Is Nothing Then
MsgBox "The cell changed"
Else
MsgBox "The cell did not change"
End If
End Sub



"ViestaWu" wrote:

Dear all,

I want to start programming a sub when a value in a cell changed, the sub
will be run. How to do it ?

Thanks,
Viesta