Use function in xll in conditional formatting

[Dirk]

Hello

Is there any way to directly use a function in an xll in the formula for
conditional formatting? If I try to enter the formula I get an error
message: "You may not use references to other worksheets or workbooks for
Conditional Formatting criteria.". The only way I could get it to work was
to use a vba function that calls into the xll.

Thanks

 

[Tom Ogilvy]

For a range on another sheet, you can create a defined name that refers to
that range, then use the defined name in conditional formatting. You might
try the same thing with the xll function.

 

[Dirk]

I'm not sure what you mean. Let's say I have a function xllIsValid in an xll
which is available to the workbook either through the addin manager or
registered in Workbook_Open.
I enter the following formula in conditional formatting:
=NOT(xllIsValid(E13:E18)) // make font red
Then I get the mentioned error.

If I enter:
=NOT(vbaIsValid(E13:E18)) // make font red
everything works.

vbaIsValid is defined in a Module of the current workbook
Function vbaIsValid(rng as Range) as Variant
vbaIsValid = Application.Run ("xllIsValid", rng)
End Function

So I don't think the range is the problem because it is from the current
worksheet.

 

[Tom Ogilvy]

I didn't say the range was a problem. You get the same error if you use a
range from another worksheet - so if that approach solves that error, it
might solve your error.

insert => Name => Define
Name: IsValid
Refersto: =NOT(xllIsValid(Sheet1!$E$13:$E$18))

then in conditional formatting
Formula is:

=IsValid

 

[Dirk]

Thanks a lot, that works great.
Fortunately I don't have to use the complete formula in "Refers to:". It is
enough to use =xllIsValid and then substitute the original name by the
defined named in the formatting formula.
I had already tried something like this after your first reply but forgot
the '=' sign.