Can someone please explain why this little macro won't work?

TIA
Len
_____________

Dim Msg

Private Sub Worksheet_Change(ByVal Target As Range)
Msg = "You must NOT check your own work!"
If Target.Column < 6 Or Target.Column < 9 Then Exit Sub
If Target.Column = 6 And Target = Target.Offset(0, -1) Then
MsgBox (Msg)
End If
If Target.Column = 9 And Target = Target.Offset(0, -2) Then
MsgBox (Msg)
End If
End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Len,

It is helpful if you describe what specific problem you are having with the
procedure -- e.g., what is does do that it shouldn't or what it doesn't do
that it should -- rather than simply saying that is doesn't work.

I suspect that your problem is with the line

If Target.Column < 6 Or Target.Column < 9 Then Exit Sub

Here, you will exit the sub regardless of what the value of Target.Column
is. If Target.Column = 6, then "Target.Column = 9" will return True, and you
will exit the sub. Similarly, if Target.Column = 9, then "Target.Column =
6" will return True and you will exit the sub. Finally, if Target.Value =
any other number, but conditionals will return True and you will exit the
sub.

You should change the "Or" to an "And". E.g.,

If Target.Column < 6 And Target.Column < 9 Then Exit Sub

In this case, you will exit the sub if Target.Column is any value other than
6 or 9.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com



"evillen" wrote in message
...
Can someone please explain why this little macro won't work?

TIA
Len
_____________

Dim Msg

Private Sub Worksheet_Change(ByVal Target As Range)
Msg = "You must NOT check your own work!"
If Target.Column < 6 Or Target.Column < 9 Then Exit Sub
If Target.Column = 6 And Target = Target.Offset(0, -1) Then
MsgBox (Msg)
End If
If Target.Column = 9 And Target = Target.Offset(0, -2) Then
MsgBox (Msg)
End If
End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Hi Chip

Sorry about not putting more detail - you are quite right about that
and you were also correct with your analysis of my syntax problem.

Many thanks
Len



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/