Excel / VB help

[Andrew]

Product: Excel 97 SR-2

I know that if I right-click on a worksheet tab, and click
the 'View Code' option, a VB editor shows up and I can
enter the folowing:

Private Sub Worksheet_Change(ByVal Target As Range)
'
'Enter VB commands to do something when worksheet
changes
'
'
End Sub

However, being totally self-taught from the on-line help,
I'm having difficulty trying to find a way so commands are
executed instead of whenever a change is made somewhere on
the entire worksheet, rather only when a change occurs
within a named range of cells within the worksheet. Maybe
something like...

Private Sub Worksheet_Change(ByVal Target As Range)
'
'if Range("DataRange").Change
'then
'Enter VB commands to do something when range
changes
'endif
'
'
End Sub

Or is there some way to define the "Target" range?


The named range "DataRange" is located on Worksheet
(1).Range("B2:B52")

Any Excel / VB experts out there that could give me some
help?

- Andrew

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("DataRange")) Is Nothing Then
'Do something
End If
End Sub

 

[Andrew]

I don't quite follow the relationship of using Intersect
between Target and Range("DataRange"). What is this doing
exactly?

- Andrew

 

[JE McGimpsey]

Did you check VBA Help? There's quite a good example there.

Intersect returns the range which both range arguments have in common.

 

[Guest]

Yes I did, but I don't see how this is detecting if a
change has occurred within a range of cells (as said
previously, I'm still very much finding my way with VB,
its syntax and commands are very different to anything
else I've worked with so it's slow going).

- Andrew

 

[JE McGimpsey]

A Worksheet_Change() event is fired whenever a cell in the worksheet has
a manual or remote entry. It passes the range that's selected to the
Worksheet_Change() macro's Target argument.

Taking the intersection between the Target and the range you want to
monitor determines whether the selected range had cells in common with
the monitored range.

If not (i.e., the intersection is Nothing), then the rest of the code is
skipped. If the selected range has cells in common with the monitored
range, the code within the If...End If block is executed.

 

[Andrew]

Ahhh, thank you, making more sense to me now.

- Andrew