VBA and Conditional Formatting to fill cells with X colour if they contain a comment

What I'm trying to do is colour / fill a cell with a colour if that cell has a comment.

I'm taking inspiration from this post here:

<I'm not allowed to post links until I post few times>


What I'm trying is insert this code into vba

Function IsComm(CellComm As Range) As Boolean
Application.Volatile
IsComm = False
If Not CellComm.Comment Is Nothing Then IsComm = True
End Function

So what I've done is:

1. hit ALT+F11
2. right-click in the VBA project window
3. click insert module
4. copy and paste the code above
5. and then click save and save the excel worksheet with macros enabled

I then add conditional formating logic by:

1. clicking home
2. clicking conditional formating
3. clicking new rule
4. use formula to determine which cells to format
5. type exactly this "=IsComm(A1)" without quotes in the "format value where this formula is true" box.
6. select the colour green from the format button and fill tab.

below are some pictures of me suffering:

Hi, welcome to the forum,

The only thing that I can see, is that you are formatting cell B7, while you want to format cells A1 or B1

For me, this works :-)

Cheers
Erwin

Hi, skite0,

for me I would prefer a macro over a function I would have to fill in for a number of cells to check them.

Sub EF1341168()

  Dim objComm As Comment
   
  With ActiveSheet
    If .Comments.Count = 0 Then
      MsgBox "No comments on ActiveSheet", , "No work to do"
      Exit Sub
    Else
      MsgBox "# of comments on Activesheet = " & .Comments.Count, , "# Comments"
    End If
    For Each objComm In ActiveSheet.Comments
      objComm.Parent.Interior.ColorIndex = vbRed
    Next objComm
  End With
 
End Sub

Ciao,
Holger

I still can not get this to work, I do not understand why though. I've attached a picture of what I have in my conditional formating window:


B.png

I'm not sure if i'm missing something obvious but the way I think this should work is, I put in the conditional formating statement as in the picture (attached to this post), and then I right click a cell and click new comment and then type something and hit enter at which point I expected the cell to turn green.

The excell is saved as xlsm

If it is acceptable, can someone upload their excel sheet so I can better compare and contrast to see what I did wrong?

Newer versions of xl have two types of comments, Comment or Note
2021-02-15_2-18-07.jpg

If you want the original comment you need to insert a "note", then your UDF would work.
If you want to insert a threaded comment you need to change the UDF slightly.

Function IsComm(CellComm As Range) As Boolean
    Application.Volatile
    IsComm = False
    If Not CellComm.CommentThreaded Is Nothing Then IsComm = True
End Function