Limit change to specific ranges in VBA routine

M

mzehr

Hi,
Bob Phillips had a wonderful VBA routine which I have
adopted and modified, and it works fine. However, I want
to limit the range of the routine to only work if the
change is made in the following ranges (D8:D27; D29:D44;
D46:D68) rather than to occur anywhere in column D (which
is column = 4 in the routine).

Any ideas?
I am using Excel 2000

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
On Error Resume Next
With Target
If .Column = 4 Then
If .Value = "a" Then
.Value = "q"
.Font.Name = "Monotype Sorts"
.Offset(0, 1).Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
.Offset(0, 1).Value = Format(Date, "dd mmm
yyyy")
End If
.Offset(0, 1).Activate
End If
End With
End Sub

Thanks,
Mike
 
F

Frank Kabel

Hi
try replacing the line
If .Column = 4 Then

with
If not intersect(me.range("D8:D27"),me.Range
("D29:D44"),me.range("D46:d48"),target) is nothing then
 
M

mzehr

Frank,
Thanks for the quick response! I replaced the code as you
suggested and unfortunately, when I click on say, D10
absolutely nothing happens, whereas at least before the
check mark would properly appear and the date would show
up in E10.

Any other ideas?

Mike
 
M

mzehr

Frank,
I figured out a workaround. If I name the identified
range as CheckRange and change the coding as follows, then
it works.

If not intersect(me.range("CheckRange"),target) is nothing
then

Thanks again!!!!!
Mike
 
J

Jack Sons

Mike,

What was Bob Phillips' routine about? Where can I find it? Or better, can
you re-post it?

Jack Sons
The Netherlands
 
D

Dave Peterson

I like the named range approach, too.

But this would also work:

If Not Intersect(Me.Range("D8:D27,D29:D44,d46:d68"), target) Is Nothing Then
 
D

Dave Peterson

Hm. It was in the original post and was quoted at the bottom of your response.
 
Top