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.

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 <ipfolc$n40$(E-Mail Removed)>, isabelle <(E-Mail Removed)> 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 <ipfpmj$n40$(E-Mail Removed)>, isabelle <(E-Mail Removed)> 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.

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 <ipfpmj$n40$(E-Mail Removed)>, isabelle <(E-Mail Removed)> 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 <i...@v.org> 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