Detecting a Comment for Conditional Formatting

BB38

New Member
Joined
Jan 16, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,713
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Are you referring to the new threaded comments, or the old style comments now called Notes?
 

BB38

New Member
Joined
Jan 16, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,713
Office Version
  1. 365
Platform
  1. Windows
OK, this needs to go in a regular code module (in the VB Editor select "Insert" on the menu bar & then Module)
VBA Code:
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
Excel Formula:
=HasComment(A1)
Change the A1 to the first cell in the applies to range.
 
Solution

BB38

New Member
Joined
Jan 16, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

OK, this needs to go in a regular code module (in the VB Editor select "Insert" on the menu bar & then Module)
VBA Code:
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
Excel Formula:
=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

New Member
Joined
Jan 16, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,713
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,713
Office Version
  1. 365
Platform
  1. Windows
For notes you would use
VBA Code:
   HasComment = Not Cl.Comment Is Nothing
or for both
VBA Code:
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

New Member
Joined
Jan 16, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
For notes you would use
VBA Code:
   HasComment = Not Cl.Comment Is Nothing
or for both
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,713
Office Version
  1. 365
Platform
  1. Windows

Forum statistics

Threads
1,143,835
Messages
5,721,054
Members
422,338
Latest member
hat360

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top