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

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.

--
Regards,
Tom Ogilvy


"Marcus B" wrote:

> 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
>

Worked a charm

thanks,
Marcus

"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Marcus B" wrote:
>
> > 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
> >