Formating cells with comments attached
 

Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. I have searched for an answer, but haven't found one yet.
Thanks.

Formating cells with comments attached
 

Format conditionnel sur commentai

http://cjoint.com/?jqg643N3cc

=comment(A2)="XX"

Function Comment(c)
Application.Volatile
If c.Comment Is Nothing Then
Comment = ""
Else
Comment = Replace(c.Comment.Text, Chr(10), "")
End If
End Function

JB
http://boisgontierjacques.free.fr


On 16 sep, 05:43, Carter wrote:
Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. *I have searched for an answer, but haven't found one yet.
Thanks.

Formating cells with comments attached
 

On Sep 15, 11:59*pm, JB wrote:
Format conditionnel sur commentai

http://cjoint.com/?jqg643N3cc

=comment(A2)="XX"

Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If
End Function

JBhttp://boisgontierjacques.free.fr

On 16 sep, 05:43, Carter wrote:



Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. *I have searched for an answer, but haven't found one yet.
Thanks.- Hide quoted text -

- Show quoted text -

OK, although I am very inexperienced with programming, I gather that
you intend for this code:

"Function Comment(c)
Application.Volatile
If c.Comment Is Nothing Then
Comment = ""
Else
Comment = Replace(c.Comment.Text, Chr(10), "")
End If
End Function"

to be placed into a code module for the worsheet, and for the formula,
=comment(A2)="XX", to be placed into the "Format values where this
formula is true:" box of the conditional formatting dialog. Well, I
did all that, chose the format I wanted to be applied, changed "A2" to
be a cell with a comment attached, nothing happened—no formatting, no
error message, nothing.
Am I supposed to replace the "XX" with something specific to my
situation? Or perhaps I misunderstood the idea. Could you please
explain it in greater detail for me?
Thanks.

Formating cells with comments attached
 

Copy this UDF to a general module in your workbook.

Function IsComment(Cell)
Set cmt = Cell.Comment
If Not cmt Is Nothing Then
IsComment = True
End If
End Function

In CFFormula is: =IsComment(A1)

Format to a nice color from Pattern


Gord Dibben MS Excel MVP

On Tue, 15 Sep 2009 20:43:21 -0700 (PDT), Carter
wrote:

Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. I have searched for an answer, but haven't found one yet.
Thanks.

Formating cells with comments attached
 

Function of Gord Dibben may be short:

Function IsComment(c)
Application.Volatile
IsComment = Not c.Comment Is Nothing
End Function

http://cjoint.com/?jqrX1HbfG1

JB

On 16 sep, 11:35, Carter wrote:
On Sep 15, 11:59*pm, JB wrote:





Format conditionnel sur commentai

http://cjoint.com/?jqg643N3cc

=comment(A2)="XX"

Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If
End Function

JBhttp://boisgontierjacques.free.fr

On 16 sep, 05:43, Carter wrote:

Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. *I have searched for an answer, but haven't found one yet.
Thanks.- Hide quoted text -

- Show quoted text -

OK, although I am very inexperienced with programming, I gather that
you intend for this code:

"Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If

End Function"


to be placed into a code module for the worsheet, and for the formula,
=comment(A2)="XX", to be placed into the "Format values where this
formula is true:" box of the conditional formatting dialog. *Well, I
did all that, chose the format I wanted to be applied, changed "A2" to
be a cell with a comment attached, nothing happened—no formatting, no
error message, nothing.
Am I supposed to replace the "XX" with something specific to my
situation? *Or perhaps I misunderstood the idea. *Could you please
explain it in greater detail for me?
Thanks.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Formating cells with comments attached
 

Thanks JB

Much more concise.


Gord

On Wed, 16 Sep 2009 08:50:09 -0700 (PDT), JB
wrote:

Function of Gord Dibben may be short:

Function IsComment(c)
Application.Volatile
IsComment = Not c.Comment Is Nothing
End Function

http://cjoint.com/?jqrX1HbfG1

JB

On 16 sep, 11:35, Carter wrote:
On Sep 15, 11:59*pm, JB wrote:





Format conditionnel sur commentai

http://cjoint.com/?jqg643N3cc

=comment(A2)="XX"

Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If
End Function

JBhttp://boisgontierjacques.free.fr

On 16 sep, 05:43, Carter wrote:

Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. *I have searched for an answer, but haven't found one yet.
Thanks.- Hide quoted text -

- Show quoted text -

OK, although I am very inexperienced with programming, I gather that
you intend for this code:

"Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If

End Function"


to be placed into a code module for the worsheet, and for the formula,
=comment(A2)="XX", to be placed into the "Format values where this
formula is true:" box of the conditional formatting dialog. *Well, I
did all that, chose the format I wanted to be applied, changed "A2" to
be a cell with a comment attached, nothing happened—no formatting, no
error message, nothing.
Am I supposed to replace the "XX" with something specific to my
situation? *Or perhaps I misunderstood the idea. *Could you please
explain it in greater detail for me?
Thanks.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Formating cells with comments attached
 

On Sep 16, 1:15*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Thanks JB

Much more concise.

Gord

On Wed, 16 Sep 2009 08:50:09 -0700 (PDT), JB
wrote:



Function of Gord Dibben may be short:

Function IsComment(c)
*Application.Volatile
*IsComment = Not c.Comment Is Nothing
End Function

http://cjoint.com/?jqrX1HbfG1

JB

On 16 sep, 11:35, Carter wrote:
On Sep 15, 11:59*pm, JB wrote:

Format conditionnel sur commentai

http://cjoint.com/?jqg643N3cc

=comment(A2)="XX"

Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If
End Function

JBhttp://boisgontierjacques.free.fr

On 16 sep, 05:43, Carter wrote:

Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. *I have searched for an answer, but haven't found one yet.
Thanks.- Hide quoted text -

- Show quoted text -

OK, although I am very inexperienced with programming, I gather that
you intend for this code:

"Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If

End Function"

to be placed into a code module for the worsheet, and for the formula,
=comment(A2)="XX", to be placed into the "Format values where this
formula is true:" box of the conditional formatting dialog. *Well, I
did all that, chose the format I wanted to be applied, changed "A2" to
be a cell with a comment attached, nothing happened—no formatting, no
error message, nothing.
Am I supposed to replace the "XX" with something specific to my
situation? *Or perhaps I misunderstood the idea. *Could you please
explain it in greater detail for me?
Thanks.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -- Hide quoted text -

- Show quoted text -

My thanks to you both. I will try this out as soon as I can.

Formating cells with comments attached
 

On Sep 17, 1:41*am, Carter wrote:
On Sep 16, 1:15*pm, Gord Dibben <gorddibbATshawDOTca wrote:





Thanks JB

Much more concise.

Gord

On Wed, 16 Sep 2009 08:50:09 -0700 (PDT), JB
wrote:

Function of Gord Dibben may be short:

Function IsComment(c)
*Application.Volatile
*IsComment = Not c.Comment Is Nothing
End Function

http://cjoint.com/?jqrX1HbfG1

JB

On 16 sep, 11:35, Carter wrote:
On Sep 15, 11:59*pm, JB wrote:

Format conditionnel sur commentai

http://cjoint.com/?jqg643N3cc

=comment(A2)="XX"

Function Comment(c)
* *Application.Volatile
* *If c.Comment Is Nothing Then
* * *Comment = ""
* *Else
* * *Comment = Replace(c.Comment.Text, Chr(10), "")
* *End If
End Function

JBhttp://boisgontierjacques.free.fr

On 16 sep, 05:43, Carter wrote:

Hi,
I would like to learn if there is way of using a formula to apply
conditional formatting only to cells that have comments attached to
them. *I have searched for an answer, but haven't found one yet.

Formating cells with comments attached
 

Good to hear.


Gord

On Thu, 17 Sep 2009 00:01:42 -0700 (PDT), Carter
wrote:

Well I tried them both and both worked perfectly. Thanks you both very
much.

Russ