Hi JLGWhiz -
Although I've never seen it officially documented, it's not possible to use
a comarison operator to compare a variable that has a value of "Nothing" to a
value. In other words, the "isect>0" comparison in your original post was
causing the error. As JE notes, the procedure triggers itself and during the
'second' round of execution, the variable isect is set to Nothing. That is
when the comparison isect>0 fails (the comparison of "...Nothing>0..." is not
permitted).
In this case, I consider the error dialog misleading: "Object variable.....
not set". To see why this is misleading, reproduce the error. In debug
mode, highlight the right side of the following statement and press the
QuickWatch button:
Set isect = Application.Intersect(Target, Range("E:E"))
The QuickWatch will confirm that the right side of the equation has a value
of "Nothing." So, in essence isect actually IS set to a value. It just so
happens that the value to which it is set (Nothing) is not permitted in the
statement that follows.
One final demonstration of this is to consider the following three statements:
Dim isect As Range
Set isect = Range("A1")
Set isect = Nothing
If you step through these statements and watch the value of isect, you'll
see that it is actually 'set' to Nothing in the third statement. That's
different than isect having a default value of Nothing because it failed to
be set.
So, all that being said, here is a solution that uses nested IF statements
to preclude the problemmatic comparison from occuring (as does JE's approach):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("E:E"))
If Not isect Is Nothing Then
If IsNumeric(isect) And isect > 0 Then
Me.Range("B" & Target.Row).Value = Me.Range("C" & Target.Row).Value
End If
End If
End Sub