MrExcel Publishing
Your One Stop for Excel Tips & Solutions

conditional formatting


Posted by Jcassell on August 22, 2001 1:59 PM

I'm trying to make a simple calendar that starts with a variable date.... for example right now I'm looking at the time period starting August 27,2001. What I want to do is have the dates for the rest of the beginning month (August in this case) be highlighted is one color, and all the dates in the next month be highlighted in a different color. I figured it would be simple enough to use the ISODD function in the conditional format formula like "=ISODD(MONTH(E10)" so even numbered months would return a value of FALSE and odd numbered months would return a value of TRUE. I can write the formula in a cell and reference the date and see the results of the ISODD function but when I try to use the same formula in conditional formatting I get the message "you may not use references to other worksheets or workbooks for conditional formatting criteria." Yes, it's all in the same worksheet.


Posted by Mark W. on August 22, 2001 2:06 PM

ISODD() is not a built-in Excel function. It's
made available via the Analysis ToolPak add-in
which -- guess what -- is another workbook, albeit
a specialized one. Use =MOD(MONTH(E10),2)<>0
in place of ISODD(), and =MOD(MONTH(E10,2)=0 in
place of ISEVEN().

Posted by Mark W. on August 22, 2001 2:08 PM

Oops! Typo...

Use =MOD(MONTH(E10),2)<>0 in place of ISODD(),
and =MOD(MONTH(E10),2)=0 in place of ISEVEN().