Automatically change cell comments

[Guest]

Hi,

I have a range of cells where the user will be able to select an option code
from a data validation list.

I would like to be able to automatically update cell comments to provide a
usefull description depending on which option code has been selected from the
validation list.

I have the following code which works for a single cell and updates the
referenced comment for that cell.

How can I change this so it will work for any cell within a specified range
and update the comment for that cell ?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F2")) Is Nothing Then
If Target.Value = "RM4" Then
Comments(1).Text Target & " " & Sheet4.Range("C4")
ElseIf Target.Value = "RM5" Then
Comments(1).Text Target & " " & Sheet4.Range("C6")
ElseIf Target.Value = "" Then
Comments(1).Text "Selection Required"
End If
End If
end sub

thanks,
Marcus

 

[Guest]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F2:F10")) Is Nothing Then
If Target.Value = "RM4" Then
Target.Comment.Text Target & " " & Sheet4.Range("C4")
ElseIf Target.Value = "RM5" Then
Target.Comment.Text Target & " " & Sheet4.Range("C6")
ElseIf Target.Value = "" Then
Target.Comment.Text "Selection Required"
End If
End If
End Sub

Works, but assumes the comment for the cell already exists.

 

[Guest]

Worked a charm

thanks,
Marcus

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("F2:F10")) Is Nothing Then
If Target.Value = "RM4" Then
Target.Comment.Text Target & " " & Sheet4.Range("C4")
ElseIf Target.Value = "RM5" Then
Target.Comment.Text Target & " " & Sheet4.Range("C6")
ElseIf Target.Value = "" Then
Target.Comment.Text "Selection Required"
End If
End If
End Sub

Works, but assumes the comment for the cell already exists.