#Excel: Using Conditional Formatting to Highlight Cells Containing User-Defined Functions by David Hager

Conditional fomatting (CF) in Excel is a powerful tool for highlighting cells that meet certain criteria based on a formula. However, to my knowledge a worksheet formula has never been used with CF to highlight cells containing user-defined functions (UDF). Presented here is a method to accomplish this.

In order to lookup whether a formula is a UDF, a lookup table of all Excel worksheet functions is required. I found the list for this at this Microsoft site.

https://support.office.com/en-us/article/Excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

The list is in the range A2:A472 in the example workbook. It was defined as xlFunctionList. I tried to just name an array of function names, but it exceeded the number of characters allowed.

So, with E2 as the active cell, I created the following defined name formula:

IsUDF=ISERROR(MATCH(MID(FORMULATEXT(E2),2,FIND(“(“,FORMULATEXT(E2))-2),xlFunctionList,0))

The formula =MID(FORMULATEXT(E2),2,FIND(“(“,FORMULATEXT(E2))-2) locates the first occurrence of the left parens. This value in used by the MID function to return a string with the function name. Then, the MATCH function looks up whether that string is in the function list. If it is not there the ISERROR function will return True. Thus, when applied as conditional formatting to cells E2:F2, E2 has a yellow highlight, indicating that a UDF function is in that cell. F2, containing a native function, is not highlighted. Note, though, that this technique only works if the function is at the beginning of the formula. Perhaps a followup to this article will deal with that issue.

isudf1

You can download the example file here:

IsUDF

Advertisements

One thought on “#Excel: Using Conditional Formatting to Highlight Cells Containing User-Defined Functions by David Hager

  1. Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s