Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting conditional on absence of comment
I have a spreadsheet which has conditional formatting in certain cells. I
would like to add as a condition that the cell does not contain a comment. I want to use built-in conditional formatting as opposed to VBA coding. Can that be done? -- Lon Sarnoff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting conditional on absence of comment
To the best of my knowldge... No... you would need to use VBA. That being
said if you were willing to insert a column next to the target of your conditional formatting (the column could be hidden) then by creating a user defined function in VBA (which you would use like any other Excel function) you could create a flag (True or False) in the hidden column, which would indicate whether the target cell has a comment in it or not. That would require very minimal code... -- HTH... Jim Thomlinson "Lon Sarnoff" wrote: I have a spreadsheet which has conditional formatting in certain cells. I would like to add as a condition that the cell does not contain a comment. I want to use built-in conditional formatting as opposed to VBA coding. Can that be done? -- Lon Sarnoff |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting conditional on absence of comment
I'm sorry, you do not even need the extra column. You can use "User Defined
Functions" in conditional formats. If you choose to go this option here is the user defined function you would need. Put this in a standard code module and you will be good to go... Public Function HasComment(ByVal Cell As Range) As Boolean If Cell.Comment Is Nothing Then HasComment = False Else HasComment = True End If End Function In you conditional format (for cell A1 for example) add the following formula =HasComment(A1) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: To the best of my knowldge... No... you would need to use VBA. That being said if you were willing to insert a column next to the target of your conditional formatting (the column could be hidden) then by creating a user defined function in VBA (which you would use like any other Excel function) you could create a flag (True or False) in the hidden column, which would indicate whether the target cell has a comment in it or not. That would require very minimal code... -- HTH... Jim Thomlinson "Lon Sarnoff" wrote: I have a spreadsheet which has conditional formatting in certain cells. I would like to add as a condition that the cell does not contain a comment. I want to use built-in conditional formatting as opposed to VBA coding. Can that be done? -- Lon Sarnoff |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting conditional on absence of comment
Thanks for your responses. I have not yet incorporated your suggestion. I
assume the user defined function can simply be added to the existing AND function within the conditional formula. I would be curious to see what response you get to your subsequent posting about what invokes reevaluation of conditional formatting? -- Lon Sarnoff "Jim Thomlinson" wrote: I'm sorry, you do not even need the extra column. You can use "User Defined Functions" in conditional formats. If you choose to go this option here is the user defined function you would need. Put this in a standard code module and you will be good to go... Public Function HasComment(ByVal Cell As Range) As Boolean If Cell.Comment Is Nothing Then HasComment = False Else HasComment = True End If End Function In you conditional format (for cell A1 for example) add the following formula =HasComment(A1) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: To the best of my knowldge... No... you would need to use VBA. That being said if you were willing to insert a column next to the target of your conditional formatting (the column could be hidden) then by creating a user defined function in VBA (which you would use like any other Excel function) you could create a flag (True or False) in the hidden column, which would indicate whether the target cell has a comment in it or not. That would require very minimal code... -- HTH... Jim Thomlinson "Lon Sarnoff" wrote: I have a spreadsheet which has conditional formatting in certain cells. I would like to add as a condition that the cell does not contain a comment. I want to use built-in conditional formatting as opposed to VBA coding. Can that be done? -- Lon Sarnoff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ABSENCE RECORDS | Excel Discussion (Misc queries) | |||
Absence formula | Excel Worksheet Functions | |||
Excel comment formatting | Excel Discussion (Misc queries) | |||
Activating a comment box based on conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Comment | Excel Discussion (Misc queries) |