Conditional Formating in different cells based on comment

Question

I would like to highlight the cells in colunm A4 - A28, only when cells D4 - D28 have a comment incerted.

ie. cell A6 would be highlighted if I incert a comment in cell D6

Answer 1

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Function HasComment(r As Range) As Boolean

    Application.Volatile

    HasComment = Not r.Comment Is Nothing

End Function

Press Alt+F4 to close the Visual Basic Editor.

Select cells A4:A28. I'll assume that A4 is the active cell within the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=HasComment(D4)

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK twice.

Remark: the conditional formatting will not be updated automatically when you add or remove a comment, but it will be updated each time the workbook is recalculated. If necessary, press F9 to calculate.

Save the workbook as a macro-enabled workbook (.xlsm, .xlsb or .xls, but not .xls).

Make sure that you enable macros when you open the workbook.

Answer 2

Hi,

We need a bit of VB code for that. ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert a module and paste the code below in on the right. Close VB editor.

Back on the worksheet selct A4 then drag to select A4:A28 and then

Home tab|Conditional dormatting | New rule | Use a formula to decide which cells to format and enter this formula.

=iscomment(D4)

Click the format button | Fill tab | choose a colour and OK out.

A note of warning, entering a comment doesn't make Excel calculate so you may have to force calculation by pressing F9

Function iscomment(rng As Range) As Boolean

On Error Resume Next

iscomment = rng.Comment.Text <> ""

On Error GoTo 0

End Function