Conditional fomatting for cells containing a Comment

[Colin Hayes]

Hi all

Can someone advise how to using conditional formatting on cells
containing a Comment?

I'd be hoping to have some method of identifying these cells and then
applying a colour or border format to make it more visible and obvious
that they contain a comment. I'm finding the small red triangle a little
hard to spot sometimes.


Grateful for any help.

 

[isabelle]

hi,

there's no formula to verify that cell contain a comment, but you can create one, and use it in conditional formatting

=NOT(ISERROR(HasComment(A1)))


Function HasComment(rng As Range) As Boolean
If Not IsError(rng.Comment.Text) Then HasComment = True
End Function

or in another way, just execute this macro

Sub MyCommentsMoreVisible()
With Cells.SpecialCells(xlCellTypeComments)
.Interior.ColorIndex = 33
End With
End Sub

 

[Colin Hayes]

hi,

there's no formula to verify that cell contain a comment, but you can create one,
and use it in conditional formatting

=NOT(ISERROR(HasComment(A1)))


Function HasComment(rng As Range) As Boolean
If Not IsError(rng.Comment.Text) Then HasComment = True
End Function

Hi

Thanks Isabelle.

I know where to put the first formula for the conditional formatting
element , but I'm not sure how or where to put the Function code.

Could you advise?

Thanks

 

[isabelle]

hi,

sorry Colin, forget the formula, it's a bad idea, because there's no recalculation even adding Application.Volatile
but you can execute the macro

 

[Colin Hayes]

hi,

sorry Colin, forget the formula, it's a bad idea, because there's no recalculation
even adding Application.Volatile
but you can execute the macro

Hi Isabelle

OK Thank you.

Could an extra line be added to the macro to return the cells to white
if the comment is deleted?

Thanks again.

 

[isabelle]

hi,

no, because problem is that adding or suppressing comments doesn't cause any events,
so there's isn't exist way to detect this changes.

 

[isabelle]

You must run the macro manually,
to return the cells to white, see Run's answer

 

[Ram]

You must run the macro manually,
to return the cells to white, see Run's answer

hi