VBA EXCEL. Run time error '13' type mismatch

[benjinorth]

Hi All,

Having a slight issue, the code I have below works ok if I change each cell in column A individually, however if i try to copy and paste the Target Value over multiple cells in column A I get a run time error '13' type mismatch.
I've highlighted the code where it "debugs"

If any one has any suggestions I'd be very grateful.

Sub worksheet_change(ByVal Target As Range)
Dim lastrow As Integer
lastrow = Range("A" & Rows.Count).End(xlUp).Row
If Not Intersect(Target, Range("A2:A" & lastrow)) Is Nothing Then
[COLOR=#ff0000]    If Target.Value = "0) N/A - Client not eligible" Then[/COLOR]
        Target.Offset(, 223) = vbNullString
        Target.Offset(, 224) = vbNullString
        Target.Offset(, 226) = vbNullString
        Target.Offset(, 227) = vbNullString
    Else
End If
End If
End Sub

 

[steve the fish]

Try replacing it with this:

Sub worksheet_change(ByVal Target As Range)
Dim lastrow As Integer
lastrow = Range("A" & Rows.Count).End(xlUp).Row
If Not Intersect(Target, Range("A2:A" & lastrow)) Is Nothing Then
    For Each c In Intersect(Target, Range("A2:A" & lastrow))
        If c.Value = "0) N/A - Client not eligible" Then
            c.Offset(, 223) = vbNullString
            c.Offset(, 224) = vbNullString
            c.Offset(, 226) = vbNullString
            c.Offset(, 227) = vbNullString
        End If
    Next
End If

End Sub

 

[benjinorth]

That works perfectly thank you!!