PDA

View Full Version : Condiitonal formatting dependent on a Comment



anthony20069
12-14-2010, 09:40 AM
Hi all,

Is there a way with VBA to use conditional formatting depending if there is a comment in a cell or not?

i tired with the following:
Function has_comment(mycell As Object) As Boolean

If mycell.Comment.Visible = False Then
has_comment = False
Else
has_comment = True
End If
End Function


Then set the conditional format formula to

=NOT(ISERROR(has_comment(A1))) for cell A1, but that didnot work - also need to to cover the range A1:ZZ1000

anyone got any ideas?


cheers

Bob Phillips
12-14-2010, 10:02 AM
Try this



Function has_comment(mycell As Range) As Boolean
Dim cmt As Comment
On Error Resume Next
Set cmt = mycell.Comment
has_comment = Not cmt Is Nothing
End Function

kroz
12-15-2010, 12:55 AM
Hi all,

Is there a way with VBA to use conditional formatting depending if there is a comment in a cell or not?

i tired with the following:
Function has_comment(mycell As Object) As Boolean

If mycell.Comment.Visible = False Then
has_comment = False
Else
has_comment = True
End If
End Function


Then set the conditional format formula to

=NOT(ISERROR(has_comment(A1))) for cell A1, but that didnot work - also need to to cover the range A1:ZZ1000

anyone got any ideas?


cheers

=NOT(ISERROR(has_comment(A1))) will only be translated to NOT(ISERROR(true/false)) and since TRUE/FALSE will never be an error...

anthony20069
12-16-2010, 08:47 AM
Try this



Function has_comment(mycell As Range) As Boolean
Dim cmt As Comment
On Error Resume Next
Set cmt = mycell.Comment
has_comment = Not cmt Is Nothing
End Function


Okay, so i put that into a new modul with =NOT(ISERROR(has_comment(A1))) as the conditional format by formula. But it did not work. What am I doing wrong?

Bob Phillips
12-16-2010, 09:05 AM
Just use

=has_comment(A1)