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]

You could use this custom function:

Function CommentText(Cell As Range, Txt As String) As Boolean
    Dim x As Comment
    Set x = Cell.Comment
    If x Is Nothing Then
        CommentText = False
    Else
        CommentText = x.Text Like "*" & Txt & "*"
    End If
End Function

like this:

=CommentText(A1,"abc")

where A1 is the cell you want to format and "abc" is in the comment's text.

 

[mona4]

Sorry, but where do i add "=CommentText(A1,"abc")"?

 

[Andrew Poulsom]

In Conditional Formatting, Formula is.

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. Then you can use the function in a cell or as a formula in Conditional Formatting.

 

[mona4]

"=CommentText(A1,"abc")

How can i change this so this applies to the entire column and not one cell

Also,
Once i have copied the code in VB Editor and put the text above in conditional formatting, do i just run the code?

 

[Andrew Poulsom]

Select the entire column and make sure A1 is the active cell. Then choose Format|Conditional Formatting, choose Formula is, paste the formula into the formula box, choose the Format and click OK twice. The A1 is a relative reference so the formula will automatically adjust for the other cells selected.

You don't need to run the code. It is a function that you have used as above.

 

[mona4]

I have done as you said and formatted to highlight the cell, but it doesnt work, what could i be doing wrong

 

[Andrew Poulsom]

It could be that none of your comments contain the text "abc". You need to change that to the text you are looking for.

 

[RDK]

I have done as you said and formatted to highlight the cell, but it doesnt work, what could i be doing wrong

You may also want to make sure you're doing a recalc [F9] after setting your formatting.

 

[mona4]

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?