Run a macro on a cell in specified range when cell changes

[Joker4321]

Hi, I have the following piece of code and I want to run it when a cell in a specified ranges is changed.

Private Sub worksheet_change(ByVal target As Range)


target.Interior.ColorIndex = 8
On Error Resume Next
Dim curcomment As String
curcomment = ""
curcomment = target.Comment.Text
If curcomment <> "" Then curcomment = curcomment & Chr(10)
target.AddComment
target.Comment.Text Text:=curcomment & ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & "Rev. " & Format(Date, "dd-mmm-yyyy") & Format(Time, "hh:mm")
End Sub


Thanks in advance.

 

[VoG]

Try

Private Sub worksheet_change(ByVal target As Range)

If Intersect(target, Range("A1:D12")) Is Nothing Then Exit Sub

target.Interior.ColorIndex = 8
On Error Resume Next
Dim curcomment As String
curcomment = ""
curcomment = target.Comment.Text
If curcomment <> "" Then curcomment = curcomment & Chr(10)
target.AddComment
target.Comment.Text Text:=curcomment & ActiveWorkbook.BuiltinDocumentProperties("Author") & _
Chr(10) & "Rev. " & Format(Date, "dd-mmm-yyyy") & Format(Time, "hh:mm")
End Sub

Change the range to suit in this line

If Intersect(target, Range("A1:D12")) Is Nothing Then Exit Sub

 

[Joker4321]

thanks for your help. Where should the code be placed in order to pickup the changes in the cells? i.e Workbook, module, pr worksheet?

 

[VoG]

Right click the sheet tab, select View Code. Then copy the code and paste it into the white window on the right of the Visual Basic Editor. Close the VBE and try it out.

 

[Joker4321]

works like a charm. Thank you!

 

[Joker4321]

is there a way to specify more than one range? As in if target is within range1 or range 2? I notice that if I use
If Intersect(target, Range("A1:D12"), Range("A15:D18")) Is Nothing Then Exit Sub

then it looks for an intersection of all 3. I tried using OR but that didn't work either

 

[VoG]

Try

If Intersect(target, Union(Range("A1:D12"), Range("A15:D18"))) Is Nothing Then Exit Sub

 

[Joker4321]

Beautiful! Thanks again VoG!