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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.