Condiitonal formatting dependent on a Comment

[anthony20069]

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:
[VBA]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
[/VBA]

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]

Try this

[vba]

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
[/vba]

[kroz]

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:
[vba]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
[/vba]

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]

Try this

[vba]

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
[/vba]

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]

Just use

=has_comment(A1)