Help with Date & Time Stamp (Comments)

Hi,

Apologies if the answer is somewhere already on here (being an amateur user of Excel, i'm bungling along and learning as I go!), I have the following code;

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> "$A$1" 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, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

Now, the problem is that I wish to extend the code to work in multiple cells contained within one column i.e. say from H1 to H99, but for the life of me I cannot seem to work a way to achieve the result I desire - can doctor the code and make it work in every cell in every column, but this isn't ideal as I really want to limit the operation of the "comments/date and time/changes" limited to one column, multiple cell only!.

Many thanks for reading!

Replace

If .Address <> "$A$1" Then Exit Sub

with (for H1:H99)

If .column <> 8 then exit sub
If .row < 100 then exit sub

If you understand the technique it's easily modified

Hi Andy,

Many thanks for the reply.......replaced the line "If .Address <> "$A$1" Then Exit Sub" with the "If .column <> 8 then exit sub
If .row < 100 then exit sub"


so the coding now is;

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 8 Then Exit Sub
If .Row < 100 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, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

Unfortunately it didn't work, so not sure what to do...........

Apologies, i'm a real amateur with these things, don't understand coding at all and trying to get this sorted for a work project which I've been tasked.........

Hi,

Apologies for re-posting......just hoping someone has the answer - a forum member very kindly posted a reply last week to a question that I posed regarding a problem i'm having with some code (unfortunately the solution didn't work). I'm trying to achieve a date and time stamp log of all entries within a specific column and range, and thus far I cannot achieve any result other than the code below working in every single cell on the spreadsheet. Ideally i'm looking to isolate the code to work in column H and from row 1-100.

Private Sub Worksheet_Change(ByVal Target As Range)
 With Target
 If .Column <> 8 Then Exit Sub
 If .Row < 100 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, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
 .Comment.Shape.TextFrame.AutoSize = True
 End With
 End Sub

Any ideas greatly appreciated!

Thanks

First.Officer,

Its against the forum rules to post duplicate threads. I have merged both your threads.

Also,

I have added code tags to your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE] before your code and [/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.