This is what I was looking for. Thanks Ken. The only problem I now have is
that although I've unprotected the comment in the format comment dropdown,
and I've re-sized it (commented out the auto size in the macro) when I
protect the sheet, the comment protection is re-set to on and my re-size
dimensions disapper. I'm assuming this is because its not an Excel comment
but one created by the macro. How do I overcome this?

" wrote:

Hi Ken,
Does this help?
Just as a demo, if the value in cell A1 of the active sheet changes to
become greater than 10 then Cell B1 has the comment "A1 is greater than
10" added.
The code must be pasted into the ThisWorkbook module. If you don't
want all sheets affected then paste the code into the WorkSheet_Change
Sub of the sheet you want affected.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If ActiveSheet.Cells(1, 1).Value 10 Then
With ActiveSheet.Cells(1, 2)
On Error Resume Next 'if no comment to delete then error is
ignored
.Comment.Delete 'remove old comment
On Error GoTo 0
.AddComment
.Comment.Visible = True 'change to False if you only want to
see the comment
'when the cursor is positioned over the
commented cell
.Comment.Text Text:="A1 is greater than 10"
.Comment.Shape.TextFrame.AutoSize = True 'comment frame size
will suit any size comment
End With
Else: On Error Resume Next
ActiveSheet.Cells(1, 2).Comment.Delete 'delete old comment because A1
is not 10
On Error GoTo 0
End If
End Sub

Ken Johnson