Can Excel Conditional Formatting use UDFs in the condition?

Question

Hi,

I have a cell in Excel that I want to format differently based on a user defined formula (UDF) - my formula tests whether there is a formula in the cell...

I am trying to use conditional formatting with my UDF to format the cell - but it does not seem to be working.

My condition is this:

="isManualPrice(R22C12)"

I tried without the quotes, but get the error

You cannot use references to other worksheets or workbooks for Conditional Formatting criteria

Perhaps the issue relates to my UDF being defined in a separate macro workbook and not my main workbook...

I see its mentioned in this blog entry from 2005, but only in passing...

Thanks in advance. Chris

Answer 1

Yes, it can.

Your problem is simply that you've got quotes in there. You need to choose "Formula Is" from the drop-down, and then your formula should be

=isManualPrice(R22C12)

...with no quotes.

(I'm more used to A1 notation rather than R1C1 notation but I assume that'll work just as well).