Conditional formatting -- referencing otherworksheets (but I'm not!)

[John_Kirkpatrick]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Excel keeps giving me the error message that, "You may not use references to other worksheets or workbooks for Conditional Formatting criteria". That would make sense, except that I am not referencing another worksheet or workbook. I am just trying to set up conditional formatting so that odd rows are blue. I tried

Formula Is =ISODD(ROW())

....and several permutations thereof, but anytime I use ISODD or ISEVEN, I get this error. Ever heard of this? Does anybody have any ideas or possible work-arounds? Thanks!

 

[CyberTaz]

Have a look at Jim Gordon's PPt presentation on how to do just that:

http://www.agentjim.com/MVP/Excel/ExcelHome.htm

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Excel keeps giving me the error message that, "You may not use references to
other worksheets or workbooks for Conditional Formatting criteria". That would
make sense, except that I am not referencing another worksheet or workbook. I
am just trying to set up conditional formatting so that odd rows are blue. I
tried

Formula Is =ISODD(ROW())

...and several permutations thereof, but anytime I use ISODD or ISEVEN, I get
this error. Ever heard of this? Does anybody have any ideas or possible
work-arounds? Thanks!

Yup! Looks like an Excel 2004 bug. This works fine in Excel 2008.

 

[JE McGimpsey]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

Excel keeps giving me the error message that, "You may not use references to
other worksheets or workbooks for Conditional Formatting criteria". That
would make sense, except that I am not referencing another worksheet or
workbook. I am just trying to set up conditional formatting so that odd rows
are blue. I tried

Formula Is =ISODD(ROW())

...and several permutations thereof, but anytime I use ISODD or ISEVEN, I get
this error. Ever heard of this? Does anybody have any ideas or possible
work-arounds? Thanks!

ISODD() in XL04 is part of the Analysis Toolpak Addin, which is where
your "referencing another worksheet) is coming from.

XL08 incorporated the ATP functions into XL, so the formula works in
that version.


Instead use

Formula Is =MOD(ROW(),2)=1

for odd and

Formula is =MOD(ROW(),2)=0

for even

 

[John_Kirkpatrick]

Great! Thanks for the workaround. I doubt I ever would have figured that out on my own.