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