Check if cell in range is empty, if it is delete comment else format comment to

I am having trouble getting this simple thing to work.

What I need:

A macro that checks cells B9:B350, if the cell is empty then delete comment to the right and if cell contains anything then format comment.

This is what i got so far:

Please Login or Register  to view this content.

I get a object variable or with block variable not set error, what can I do?

Same error happening

asd1.JPG
asd2.JPG

Please Login or Register  to view this content.

Originally Posted by AB33

Please Login or Register  to view this content.

Oh This works great!

One more question, Can we optimise it that instead of cheking all the cells at once it would only check the currenttly active cell and 1 above it?

I do not know which one is the active cell. This code checks cells in columns B starting from 9 down to the bottom.

Please Login or Register  to view this content.

Thank you AB33, this was helpful.

However this is still a workaround for me, is there any way to build it straight in my custom function? (This function acts like VLOOKUP but also retrieves the comment from found cell)
It gets the comment text fine, but messes up the formatting

Please Login or Register  to view this content.