Mallick,

Straight from XL2003 Help on intersect:

Returns a Range object that represents the rectangular intersection of two
or more ranges.

Step through the code on this to see what Intersect actually returns:
http://www.excely.com/excel-vba/usin...reate-a-range/


In the code below, Target is the cell that just changed. So,
Application.Intersect(Target, Range("myRange")) is checking whether the just
changed cell falls within the named range, myrange. If the cells intersect,
it returns and address. If not, it doesn't return anything. The double
negatives are a bit of a brain-stretcher. Think of "If Not isect Is Nothing"
as 'if isect has a value'. Alternatively, you could use the following
method:

If isect Is Nothing Then
'do nothing
Else
'code if ranges intersect
End IF


You don't need to use a named range (e.g. you could use
Application.Intersect(Target, Range("A1:B16")). However, a named range
offers advantages. The main advantage being if the target address range
changes, you don't need to open your VBA module and search for relevant
pieces of code to alter. You just need to update the named range. Taking it
to the next step, if you use a dynamic named range, you don't need to do
anything.

If my explanation was clumsy:
http://www.ozgrid.com/VBA/vba-intersect.htm

I trust Gord solved the balance of your problem.

--
Steve

"Mallick" wrote in message
...
Thanks smartin and AltaEgo for your quick replies.

AltaEgo, Your step wise answer was easy to implement but can you please
explain intersect method in general and what specific task it performs
here.

Many Thanks

Mallick

"AltaEgo" wrote:

Hi Mallick

The following should do the trick.

1) Right click the tab of the relevant worksheet
2) Click "View Code"
3) Paste
4) You also need to specify the range of cells where the code should
activate. The easiest way to do this is to use a named range. In the
sample
below, I named my cells myRange.


Private Sub Worksheet_Change(ByVal Target As Range)

Set isect = Application.Intersect(Target, Range("myRange"))
If Not isect Is Nothing Then
'replace the next line with your code
' or call your macro
MsgBox Target
msgbox Target.address

End If

End Sub




--
Steve

"Mallick" wrote in message
...
Hi
I am trying to run my macro whenever there is change of values in a
range
of
cells. For example, A table of 4x4 contains all zeros initially, I want
the
macro to run whenever the initial value in any cell changes.

Can anyone help me with this?

Many Thanks.

Mallick