Run-time error 13 - Type mismatch, when deleting cells

[lycrake]

Hi there guys

I found a sample of VBA code on these forums, when I type text into one column I want it to put the Time and Date in the next two columns. If I delete the text, I then want it to delete the date and time.

This all works fine, but I get the above error when i'm selecting multiple cells and trying to delete them (containing the date and time), or when i'm trying to copy and paste multiple cells that contain the date and time.

Here is the code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Column Mod 2 = 1 And Target.Row >= 6 Then

        If Target.Value = "" Then
            Target.Offset(0, 1).Value = ""
            Target.Offset(0, 2).Value = ""
        Else
            Target.Offset(0, 1).Value = Date
            Target.Offset(0, 2).Value = Time   
        
End If
End If
End Sub

Any ideas what i'm doing wrong?

It says the error is on the "If Target.Value = "" Then" line

Thanks

 

[lycrake]

Any ideas anybody?

I'm sure its something very simple!!!

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Column = 1 And Target.Column Mod 2 = 1 And Target.Row >= 6 Then
    For Each c In Target
        If c.Value = "" Then
            c.Offset(0, 1).Value = ""
            c.Offset(0, 2).Value = ""
        Else
            c.Offset(0, 1).Value = Date
            c.Offset(0, 2).Value = Time
        End If
    Next c
End If
End Sub

 

[lycrake]

Thats amazing, that works perfectly thanks a lot very helpful!!!