JE,

Thank you. It works like a charm. All I had to do was past it into the VB
editor. I don't yet understand why it works but I'll try to reverse engineer
it and maybe learn something.

Thanks again
Rudy

"JE McGimpsey" wrote:

I have no trouble getting more than 7 If...Then...Else options. Without
knowing exactly what you're doing, here's one alternative:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim vArray As Variant
Dim i As Long
Dim nBase As Long

vArray = Array(Array(13, 8, 70, -1, -1, 1, -1), _
Array(14, 10, 68, -2, -1, 2, -1), _
Array(15, 14, 64, -4, -1, 4, -1), _
Array(16, 22, 56, -8, -1, 8, -1), _
Array(18, 29, 31, -7, -2, 25, -2), _
Array(20, 47, 49, -25, 2, 7, 2), _
Array(22, 22, 56, -8, 2, 8, 2), _
Array(24, 14, 64, -4, 1, 4, 1), _
Array(25, 10, 68, -2, 1, 2, 1), _
Array(26, 8, 70, -1, 1, 1, 1))

nBase = LBound(vArray)
With Target
For i = nBase To UBound(vArray)
If .Column = vArray(i)(nBase) Then
If .Row vArray(i)(nBase + 1) Then
If .Row < vArray(i)(nBase + 2) Then
Range("B2").Value = _
.Offset(vArray(i)(nBase + 3), _
vArray(i)(nBase + 4)).Text
Range("AF2").Value = _
.Offset(vArray(i)(nBase + 5), _
vArray(i)(nBase + 6)).Text
Exit For
End If
End If
End If
Next i
End With
End Sub

You can extend this to include more columns if desired by adding
additional Array() statements inside the enclosing Array(...).

A couple of notes about your macro:

1) Left_Value will be declared as a Variant, but only because you didn't
provide an explicit type. Unlike C, for example, each variable on a line
has to have a type declaration or it's declared variant. For example:

Dim Left_Value, Right_Value As Double

declares Right_Value to be a Double, but Left_Value to be a Variant.
Instead,

Dim Left_Value As Double, Right_Value As Double

declares both to be Doubles.

2) Using the Evaluate method (i.e., [B2]) is slower than using the Range
Method (i.e., Range("B2")).

3) There's never a reason to use the evaluate method with constants,
e.g., [-1].

4) Use "Set" only with object variables (or variants which will refer to
objects). In this case Left_Value and Right_Value will be assigned Text
values (so you could type both with

Dim Left_Value As String, Right_Value As String

Also, since you're assigning values to the variables in the loop,
there's no real reason to clear them before assignment.

In article ,
rudawg wrote:

Thanks Don,

I have 10 conditions. The code is long and probably inefficient. But here
goes.....

Private Sub Worksheet_SelectionChange2(ByVal Target As Range)

Dim Left_Value, Right_Value As Variant

Set Left_Value = Nothing
Set Right_Value = Nothing

If Target.Column = 13 Then '<----M
If Target.Row 8 Then
If Target.Row < 70 Then
Left_Value = ActiveCell.Offset([-1], [-1]).Text
Right_Value = ActiveCell.Offset([1], [-1]).Text
[b2] = Left_Value
[af2] = Right_Value
End If
End If

ElseIf Target.Column = 14 Then '<----N
If Target.Row 10 Then
If Target.Row < 68 Then
Left_Value = ActiveCell.Offset([-2], [-1]).Text
Right_Value = ActiveCell.Offset([2], [-1]).Text
[b2] = Left_Value
[af2] = Right_Value
End If
End If

ElseIf Target.Column = 15 Then '<----O
If Target.Row 14 Then
If Target.Row < 64 Then
Left_Value = ActiveCell.Offset([-4], [-1]).Text
Right_Value = ActiveCell.Offset([4], [-1]).Text
[b2] = Left_Value
[af2] = Right_Value
End If
End If

ElseIf Target.Column = 16 Then '<----P
If Target.Row 22 Then
If Target.Row < 56 Then
Left_Value = ActiveCell.Offset([-8],
[-1]).Text
Right_Value = ActiveCell.Offset([8],
[-1]).Text
[b2] = Left_Value
[af2] = Right_Value
End If
End If

ElseIf Target.Column = 18 Then '<----R
If Target.Row 29 Then
If Target.Row < 31 Then
Left_Value = ActiveCell.Offset([-7],
[-2]).Text
Right_Value =
ActiveCell.Offset([25], [-2]).Text
[b2] = Left_Value
[af2] = Right_Value
End If
End If

ElseIf Target.Column = 20 Then '<----T
If Target.Row 47 Then
If Target.Row < 49 Then
Left_Value =
ActiveCell.Offset([-25], [2]).Text
Right_Value = ActiveCell.Offset([7],
[2]).Text
[b2] = Left_Value
[af2] = Right_Value
End If
End If

ElseIf Target.Column = 22 Then
'<----V
If Target.Row 22 Then
If Target.Row < 56 Then
Left_Value =
ActiveCell.Offset([-8], [2]).Text
Right_Value =
ActiveCell.Offset([8], [2]).Text
[b2] = Left_Value
[af2] = Right_Value
End If
End If

'ElseIf Target.Column = 24
Then '<----X
'If Target.Row 14 Then
'If Target.Row < 64 Then
'Left_Value =
ActiveCell.Offset([-4], [1]).Text
'Right_Value =
ActiveCell.Offset([4], [1]).Text
'[b2] = Left_Value
'[af2] = Right_Value
'End If
'End If

'ElseIf
Target.Column = 25 Then '<----Y
'If Target.Row
10 Then
'If Target.Row <
68 Then
'Left_Value =
ActiveCell.Offset([-2], [1]).Text
'Right_Value =
ActiveCell.Offset([2], [1]).Text
'[b2] = Left_Value
'[af2] =
Right_Value
'End If
'End If

'ElseIf
Target.Column = 26 Then '<----Z
'If
Target.Row 8 Then
'If
Target.Row < 70 Then
'Left_Value
= ActiveCell.Offset([-1], [1]).Text
'Right_Value
= ActiveCell.Offset([1], [1]).Text
'[b2] =
Left_Value
'[af2] =
Right_Value
'End If
'End If


End If
End Sub


"Don Guillett" wrote:

Perhaps it would help to post your code.

--
Don Guillett
SalesAid Software

"rudawg" wrote in message
...
I seem to be running into the same limit as I would with a function of
nested
ifs.