#Excel Short and Sweet Tip #12 (Documenting Formulas and Highlighting Those Formulas With Conditional Formatting) by David Hager

One way to document a worksheet cell is through a cell comment. That was demonstrated recently by the following article.

https://dhexcel1.wordpress.com/2017/04/29/excel-worksheet-udf-that-adds-a-comment-to-any-cell-by-david-hager/

However, there is another way to add a comment to a cell containing a formula. The N function returns a value of zero if the argument is a string (i.e. – =N(“Hi”) equals zero), so it does not affect the value of the main formula.

So, the formula to add the comment to is:

=MATCH(TRUE,ISNUMBER(FIND(“s”,$A$1:$A$20)),0)

which is an interesting formula in its own right but a discussion of it is not germane to this article.

This is the formula with the comment attached.

=MATCH(TRUE,ISNUMBER(FIND(“s”,$A$1:$A$20)),0)+N(“This formula created 05/11/17 by me.”)

In order to locate formulas of this type, a special conditional formatting formula is needed.

The formula needed to create the conditional format for cells containing formulas & documented by the N function is shown below (defined with D5 as the active cell).

HasFormulaComment=FIND(“+N(“””,FORMULATEXT(D5))

A notable feature of this formula is that since a quote is to be part of the lookup string +N(“ then a triple quote is required by Excel at the end of the string.

In the figure below, this conditional fomat is applied to column D.

 

For D5, this formula returns a value of 47. If it returns an error or 0, then the CF condition is FALSE. So, only cell D5 in column D is highlighted.

You can download the example file here.

Comment_ConditionalFormat