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

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

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

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