AlexGill89
New Member
- Joined
- Jul 25, 2018
- Messages
- 6
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.
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.
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.
Code:
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
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.