Detecting a Comment for Conditional Formatting

[BB38]

I have seen a post on here about this subject, but it may be dated.

I am an average level excel user, maybe slightly higher.

I do not do a lot of VBA and Macros but would like to start doing so.

I have a project for work that wants me to conditionally format cells in a different color that have a comment in the cell (via the comments function)

Can someone explain to me as if i'm 5 how I can make this happen? Is it possible?

Many thanks to the one who solves this.

BB

 

[Fluff]

Hi & welcome to MrExcel.
Are you referring to the new threaded comments, or the old style comments now called Notes?

 

[BB38]

Hi & welcome to MrExcel.
Are you referring to the new threaded comments, or the old style comments now called Notes?

Hi - thank you for responding. I would like to set it up for the new threaded comments. Although I would have use for it as notes as well if it proves easy to implement. Thank you.

 

[Fluff]

OK, this needs to go in a regular code module (in the VB Editor select "Insert" on the menu bar & then Module)

Function HasComment(Cl As Range) As Boolean
   Application.Volatile
   HasComment = Not Cl.CommentThreaded Is Nothing
End Function

Then in conditional formatting, New Rule, Use a formula & use

=HasComment(A1)

Change the A1 to the first cell in the applies to range.

 

[BB38]

OK, this needs to go in a regular code module (in the VB Editor select "Insert" on the menu bar & then Module)

Function HasComment(Cl As Range) As Boolean
   Application.Volatile
   HasComment = Not Cl.CommentThreaded Is Nothing
End Function

Then in conditional formatting, New Rule, Use a formula & use

=HasComment(A1)

Change the A1 to the first cell in the applies to range.

That worked! I cannot thank you enough. This is my first time on this forum and it won't be my last. Thank you.

 

[BB38]

That worked! I cannot thank you enough. This is my first time on this forum and it won't be my last. Thank you.

and if I was going to do it for the classic notes?

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

For notes you would use

   HasComment = Not Cl.Comment Is Nothing

or for both

Function HasComment(Cl As Range) As Boolean
   Application.Volatile
   HasComment = False
   If Not Cl.Comment Is Nothing Then
      HasComment = True
   ElseIf Not Cl.CommentThreaded Is Nothing Then
      HasComment = True
   End If
End Function

 

[BB38]

For notes you would use

   HasComment = Not Cl.Comment Is Nothing

or for both

Function HasComment(Cl As Range) As Boolean
   Application.Volatile
   HasComment = False
   If Not Cl.Comment Is Nothing Then
      HasComment = True
   ElseIf Not Cl.CommentThreaded Is Nothing Then
      HasComment = True
   End If
End Function

Again, thank you!

 

[Fluff]

My pleasure