Vba multiple if target column error 13

[AlexGill89]

Hi all,
I'm trying to set up a referral tracking sheet that monitors when data is entered in a cell and records the date. It was working but as I've added code it has started throwing up errors and I'm not sure if it can be fixed. Please see code below.

Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If Target.Cells.Count <> 1 Then Exit Sub

If Target.Column = 12 And .Value = "Started" And Target.Offset(0, 1).Value = "" Then

        Target.Offset(0, 1) = Format(Now(), "DD/MM/YY")

    End If

    If Target.Column = 12 And .Value = "Completed" And Target.Offset(0, 2).Value = "" Then

        Target.Offset(0, 2) = Format(Now(), "DD/MM/YY")

    End If

    If Target.Column = 5 And Target.Offset(0, -1).Value = "" Then

        Target.Offset(0, -1) = Format(Now(), "DD/MM/YY")

    End If

     If Target.Column = 16 And .Value = "Job Offer" And Target.Offset(0, 1).Value = "" Then

        Target.Offset(0, 1) = Format(Now(), "DD/MM/YY")

    End If

If Target.Column <> 9 And Target.Column <> 11 And Target.Column <> 5 Then Exit Sub

If Target.Row = 1 Then Exit Sub

If Target.Row = 2 Then Exit Sub

If Target.Row = 3 Then Exit Sub

If IsEmpty(Target) Then Exit Sub

Dim strNewText$, strCommentOld$, strCommentNew$

strNewText = .Text

If Not .Comment Is Nothing Then

strCommentOld = .Comment.Text & Chr(10) & Chr(10)

Else

strCommentOld = ""

End If

On Error Resume Next

.Comment.Delete

Err.Clear

.AddComment

.Comment.Visible = False

.Comment.Text Text:=strCommentOld & _

Format(VBA.Now, "DD/MM/YYYY at h:MM AM/PM") & Chr(10) & strNewText

.Comment.Shape.TextFrame.AutoSize = True

End With

End Sub

Column 12 tracks starts and completions (cells have list data validation on them) column 5 is initial referrals and 16 tracks the job offers. I need to know the date of when these change.

I have the comment code as the columns with the dates are hidden. This seems to work okay.

Sorry this is vague it's not something I do regularly enough.

 

[StuLux]

Could you add either a sample workbook or at least a screenshot showing examples of how your other columns are laid out to help with this.

Also details of what error(s) you are getting and on what lines of code.

 

[AlexGill89]

I had some cells with =date value set up to convert the text to actual date format. That seems to have caused the issue.

I will just have to run "text to columns" when I need to extract stats. Is there any other way of doing it? The codes purpose is to record the date whenever details are entered in those columns but it appears as text rather than "date" if that makes sense?

I will upload an image later.

 

[Fluff]

If you just want the date use

Target.Offset(0, 1) = Date