On 24 Dec 2005 17:50:14 -0800, "Ken Johnson"
wrote:

Hi StargateFan,
I'm having trouble fully understanding your situation so all I can
offer is a suggestion that might steer you towards a solution:
Have you tried the MOD function, which can be used to generate a
repeating series such as 1,2,3,1,2,3,1,2,3...

Maybe in Formula Is box = MOD(Actual Cell Address,3) = 0 for first fill
color,
then = MOD(Actual Cell Address,3) = 1 for second fill color,
then = MOD(Actual Cell Address,3) =2 for third fill color.

Does this help?

Ken Johnson

Darn, sorry. It's often very difficult to explain a situation. <g.

I have a calendar-like spreadsheet that is several pages long. There
are 3 rows that act as a header followed by 6 blank lines for user
handwritten input after printing. Then 3 more "header" rows followed
by 6 blank lines, etc., all the way down the sheet.

Those headers calculate the date depending on previous cells but it's
three cells at the very top that start the dating process. User types
in a start date and A1 is formatted as yyyy so only the year is pulled
from the date entered and that repeats for every corresponding cell in
row 1 in this and all other row 1 "header" rows in the sheet. A2 is
formatted as dddd so the day is dumped there and all other
corresponding days in headers are incremented by 1. B2 is formatted
as mmm.dd.yyyy so the complete date is used; the rest of the cells in
sheet increment by one day in similar fashion to A2 so that a yearly
calendar is achieved after user inputs a start date. (Row 3 in each
"header" has static info that doesn't change and a blank line for user
handwritten input after printout.)

However, the conditional formatting for the 3 rows in each header
could depend on the first cell, $A$1, as it's always displays in year
format. So for the 365 days in the calendar that show 2005, to give
an example, the header could have a light orange colour. But if the
user inputs a date in 2006, again just as an example, the colour would
hopefully change to a, say, light blue. 2007 would be a light green
colour and 2008 could cycle back to the light orange colour. We only
have 3 conditions available, I understand, under conditional
formatting so there are only 3 colours. But my idea is that by the
time 2008 comes along, we could go back to light orange with no
problem.

The goal is that in subsequent years, every time users print out these
calendar sheet, there'll be a different colour in the header cells for
each year. This came up because I started printing out pages for 2006
and found that same colour in both 2005 and 2006 in the binder was
confusing and we shouldn't use a separator in this case to separate
pages for each year as data needs to be easily accessible and the
binder is already overfull <g. Colour differentiation is best.

I know XL2K will be able to handle this, but no luck this time around
finding code to use in the archives. Lots of dates and conditional
formatting but nothing related to years or to this type of situation
came up.

Thanks! Hope this is a little clearer (?). :oD


MERRY XMAS!!