getting comment pop-up

[A M I T]

I use MS Excel 2007.
I have data validation list for a range of cells. There are 4 values to
choose from.

My requirement: There are 2 values which, when, selected should prompt the
use to enter a text/comment that is saved. This comment/text can bee seen by
moving the mouse over the cell.
However, if the value is changed to either of the other 2 - the comment/text
is automatically deleted.

 

[Gary''s Student]

Here is an example:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set b9 = Range("B9")
If Intersect(t, b9) Is Nothing Then Exit Sub
Application.EnableEvents = False
With t
If .Value = "first" Or .Value = "second" Then
.ClearComments
Else
MsgBox ("Please enter a comment.")
End If
End With
Application.EnableEvents = True
End Sub

 

[A M I T]

Thanks for the help.
However, it did not execute with the range specified.
The cell range in use is K7:AR73 and the values for which the popup are
reqd. are "hc" , "sc", "w".