Untested, but possibly:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim newtext$
Dim RangeToCheck As Range

for each cmt in Activesheet.Comments
cmt.Visible = False
Next

If Target.Cells.Count 1 Then Exit Sub

Set RangeToCheck = ThisWorkbook.Sheets("Rep Data").Range("headers")
Set isect = Application.Intersect(Target, RangeToCheck)

If isect Is Nothing Then Exit Sub

newtext = isect.Offset(1, 0).Value

With isect.AddComment
.Visible = True
.Text newtext
End With

End Sub

Although I am not sure why you don't just create all your comments one time
(or in the sheet activate event) and let excel perform it normal behavior of
displaying it when the mouse goes over the cell and hiding if not.

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
Thanks Tom. It's working now. However, I have one small question -
Now, everytime I select a cell in the header row, a comment pops up -
but it remains there when I move to the next one. Is there a way to set
the comments to be visible only when the user has selected that cell?

My revised code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim newtext$
Dim RangeToCheck As Range

If Target.Cells.Count 1 Then Exit Sub

Set RangeToCheck = ThisWorkbook.Sheets("Rep Data").Range("headers")
Set isect = Application.Intersect(Target, RangeToCheck)

If isect Is Nothing Then Exit Sub

newtext = isect.Offset(1, 0).Value

With isect.AddComment
.Visible = True
.Text newtext
End With

End Sub

Thanks again!

Kunal