Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting comments attached to cells into data | Excel Discussion (Misc queries) | |||
Keeping web query cells attached to other data. | Excel Worksheet Functions | |||
COUNTIF to count cells that have an attached comment | Excel Discussion (Misc queries) | |||
Excel Charts with attached comments option | Charts and Charting in Excel | |||
how can we copy cells comments text and paste to cells | Excel Discussion (Misc queries) |