cannot use ISEVEN or ISODD functions in Conditional Formatting

[Guest]

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[JE McGimpsey]

ISEVEN and ISODD are Analysis toolpak add-in functions.

You can achieve the same thing with

CF1: =MOD(ROW(),2) = 0

or

CF1: =MOD(ROW(),2) = 1

 

[Bob Phillips]

This is because CF doesn't like ranges on another worksheet. Either give the
range a name, Insert>Name>Define..., and use that, or use INDIRECT,
INDIRECT("Sheet2!A1:A20") for instance.

--

HTH

RP
(remove nothere from the email address if mailing direct)

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Gord Dibben]

ISEVEN and ISODD are funtions from the Analysis Toolpak, which is an add-in,
hence the message.

For alternating row shading based on CF, select a gaggle of rows then enter
this formula in CF>Formula is.

=MOD(ROW(),2)=1

To get 3 and 3 as above.........

=MOD(ROUND(ROW()/3,0),2)=0

Alternate........=ROW()=ODD(ROW())


Gord Dibben Excel MVP

On Tue, 6 Dec 2005 12:42:02 -0800, "Scott Paine" <Scott

 

[Guest]

Oh yeah, I learned that from C++ years ago, before purging all my programming
knowledge to make room for recipes;-) Thanks.

:...

 

[Guest]

Your reply makes little sense to me b/c the problem happens with only one
sheet and no defined ranges. The functions must call other sheets or ranges
internally or something... The other solutions are simpler, but thanks
anyway

:...

 

[Guest]

Same as JE McGimpsey's reply, only thanks for pointing out I can use this
method for more than even/odd. Interesting possibilities... Thanks.

:...