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.

On Sat, 30 Apr 2011 00:09:32 +0100, Colin Hayes wrote:


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.

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


--
isabelle

Le 2011-04-29 19:09, Colin Hayes a écrit :
Can someone advise how to using conditional formatting on cells containing a Comment?

In article , isabelle writes
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



or in another way, just execute this macro

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

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


--
isabelle

Le 2011-04-29 21:34, Colin Hayes a écrit :

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

In article , isabelle writes
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.

On Sat, 30 Apr 2011 00:09:32 +0100, Colin Hayes wrote:


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
========================

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

Le 2011-04-29 22:10, Colin Hayes a écrit :
In article , isabelle writes
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.

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

--
isabelle

Le 2011-04-29 22:44, isabelle a écrit :
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.

On Apr 30, 6:49*am, isabelle wrote:
You must run the macro manually,
to return the cells to white, see Run's answer

--
isabelle

Le 2011-04-29 22:44, isabelle a écrit :







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.

hi