Restricting VBA to certain columns

[DME]

Greetings,

I've been attempting to write VBA that will automatically add a date & time stamp as a comment to certain cells when they are modified. Based on various codes I've found, I was able to piece together the below. The only problem is that it specifically targets cell A1 only.

Does anyone know how I can modify this code to include a range of columns? I'm still creating the actual file but I'm thinking I'll need it to include Columns AB through AR only.

I'm using Excel for Mac 2011 as a FYI in case that impacts anything - hence my CHR(13) instead of CHR(10).

Many thanks in advance!

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(13) & Chr(13)
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 h:MM AM/PM") & Chr(13) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

 

[Mike H..]

Greetings,

I've been attempting to write VBA that will automatically add a date & time stamp as a comment to certain cells when they are modified. Based on various codes I've found, I was able to piece together the below. The only problem is that it specifically targets cell A1 only.

Does anyone know how I can modify this code to include a range of columns? I'm still creating the actual file but I'm thinking I'll need it to include Columns AB through AR only.

I'm using Excel for Mac 2011 as a FYI in case that impacts anything - hence my CHR(13) instead of CHR(10).

Many thanks in advance!

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(13) & Chr(13)
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 h:MM AM/PM") & Chr(13) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

Hi,

This will now do columns AB thru AR

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Intersect(Target, Range("AB:AR")) Is Nothing 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(13) & Chr(13)
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 h:MM AM/PM") & Chr(13) & strNewText
 .Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

 

[DME]

@Mike H.. this is perfect, thank you for the timely reply!