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.

 

[Ron Rosenfeld]

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.

I don't believe there is any way of using Conditional Formatting to mark cells containing a comment.

You could do it with a VBA Macro.

Something like:

====================
On Error Resume Next
With Cells.SpecialCells(xlCellTypeComments)
.Interior.Color = vbRed
.Font.Bold = True
End With
==================

Of course, you would have to ensure that you didn't change any desired formatting.

The above code snippet should at least get you started. But how best to implement it might depend on the kind of worksheet you are designing.

 

[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.

 

[Ron Rosenfeld]

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.

It seems a macro would be OK from your other comments.

Try this:

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

======================
Option Explicit
Sub MarkComments()
Dim c As Range
With ActiveSheet.UsedRange
.Interior.Color = xlNone
.Font.Bold = False
End With
On Error Resume Next
With Cells.SpecialCells(xlCellTypeComments)
.Interior.Color = vbRed
.Font.Bold = True
End With
End Sub
========================

 

[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