VBA Intersect issue

[bgrice]

Hi. I'm getting an error on the Intersect line of the code below: Object variable or With block variable not set.

The code seems logical to me but I'm a newbie. If the target intersects between C10 and F141 then I want it to perform some checks and amend if necessary. But if it doesn't intersect in that range then I want it to do nothing (exit the sub).

Can someone explain what I'm doing wrong, please? Thanks, Ben

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C10:F141")) Then
        If Target.Value = "" Then
            Target.Value = "-"
        ElseIf Target.Value = "0" Then
            Target.Value = "-"
        ElseIf IsEmpty(Target) = True Then
            Target.Value = "-"
        ElseIf IsNumeric(Target) = False Then
            Target.Value = "-"
        End If
        Target.HorizontalAlignment = xlRight
    Else
        Exit Sub
    End If
End Sub

 

[Peter_SSs]

As well as the continuous re-call of the Worksheet_Change event code, one other thing with your code in post 7 is that it checks all 528 cells in C10:F141 every time, not just the one(s) where a change has been made.