Can you conditional format cells with comments?

[mona4]

Hi Please can anyone tell me if it is possible to use conditional formatting on cells which have comments so that they change a specific colour depending on the comment.

Thanks
Mona

 

[Andrew Poulsom]

Hi

Even though i change "ABC" to the text i need, it still doesnt highlight, i have checked over and over and nothing happens....

any other suggestions?

What is the actual formula you are using and what is in the comment text?

 

[mona4]

This is an example of whats in the comment box- "91 rights issue,29/06/93 split, all other splits correct, splits resolved"

and the formula i am using is: =CommentText(A1,"splits resolved")

 

[Andrew Poulsom]

I just tested it again.

I added a comment to cell A1 with the text "91 rights issue,29/06/93 split, all other splits correct, splits resolved". In A2 I added a comment with the text "91 rights issue,29/06/93 split, all other splits correct, splits not resolved". I selected A1:A2, chose Format|Conditional Formatting, Formula is =CommentText(A1,"splits resolved"), chose Format Patterns Red and clicked OK twice. A1 had a red fill pattern, A2 had no fill pattern.

So we must be doing something differently. What do you get if you enter:

=CommentText(A1,"splits resolved")

in a cell? I get TRUE (FALSE when applied to A2).

 

[mona4]

i tested your example exactly into two cells as you did but nothing highlighted. When i put =CommentText(A1,"splits resolved") into a cell, it said #NAME?

 

[Andrew Poulsom]

It will say #NAME? if the custom function is not in a General module in the workbook where you are trying to use it. Correct that and your conditional formatting should work.

 

[mona4]

Sorry but what do you mean by a general module in the workbook? Where exactly would that be?

 

[Andrew Poulsom]

As I said previously:

In case you don't know how to create a custom function ...

Press Alt+F11 to access the Visual Basic Editor. Click your workbook in the Project window and choose Insert|Module from the menu. Paste my code in the window on the right. Press Alt+F11 again to return to your workbook.

 

[mona4]

Hi,

I repasted the code you gave me into VB Editor as you said, i also typed in the cells "=CommentText(A1,"splits resolved")" exactly as you had tested out before and both give FALSE.

None of the cells are still highlighting, i dont know what else to do, just to let u know, i do know advanced excel techniques but this one is really mind boggling, i have looked at what i might be doing wrong but it all seems fine

???

 

[Andrew Poulsom]

Are you sure you actually have a comment in A1 - ie Insert|Comment? As I said it works for me, so I am just wondering if there is a terminology issue here.

 

[mona4]

yep i do have a comment like that