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

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.

--
Regards,
Tom Ogilvy

"Dirk" <dirk.kapusta@reply_in_newsgroup wrote in message
...
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

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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Dirk" <dirk.kapusta@reply_in_newsgroup wrote in message
...
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

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

--
Regards,
Tom Ogilvy


"Dirk" <dirk.kapusta@reply_in_newsgroup wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Dirk" <dirk.kapusta@reply_in_newsgroup wrote in message
...
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

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.


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Dirk" <dirk.kapusta@reply_in_newsgroup wrote in message
...
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" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Dirk" <dirk.kapusta@reply_in_newsgroup wrote in message
...
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