User types in date in a msgbox of which year is pulled out for A1.
Was trying to develope conditional formatting for a set of selected
cells based on A1 (therefore, it's absolute as $A$1) where this year
is one colour, next year is another and following year is another - in
keeping with the 3 conditions we're allowed to have in conditional
formatting. I figure that in 4 years time, we can cycle through the
same 3 colours again with no problem.

But I'm not any closer to getting something that works even after much
searching through the archives.

The goal, then, is if I'm typing in today's date in user box, 2005 is
put in A1 and all pertinent cells are light orange. Yet if I type in
a starting date in 2006, those cells will be light blue, and anything
in 2007 would be in light green. 2008 would start back at light
orange, etc.

Can something like this be accomplished?

Thanks much.



MERRY CHRISTMAS! :oD

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

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!!

Hi StargateFan,
Maybe I am unclear to exactly what you want but it sounds like w/
year is in A1 depends on the color you want your header. So

2005 = Red
2006 = Blue
2007 = Green
etc

Well highlight your header row(s) and open up conditional formating

Condition 1
Formula is -- -=$A$1=2005-
Then choose the color of your liking

Do the same for Condition 2 and 3. Except of course A1 = 2006 and 200
and select the color you want. I hope this is what your looking for.

Merry Christmas

Jaso

--
jt
-----------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...fo&userid=2113
View this thread: http://www.excelforum.com/showthread.php?threadid=49593

On Sun, 25 Dec 2005 05:06:39 -0600, jtp
wrote:


Hi StargateFan,
Maybe I am unclear to exactly what you want but it sounds like w/e
year is in A1 depends on the color you want your header. So

2005 = Red
2006 = Blue
2007 = Green
etc

Yes, that's the concept.

Well highlight your header row(s) and open up conditional formating

Condition 1
Formula is -- -=$A$1=2005-
Then choose the color of your liking

Do the same for Condition 2 and 3. Except of course A1 = 2006 and 2007
and select the color you want. I hope this is what your looking for.

Yes, that's exactly the idea. Except the fact that to not make it
actual dates. i.e., when I leave this job, want to know that any year
they print - even after 2007 <g - each calendar will come out with a
different colour.

I guess that another option would be to have just two colours. All
odd-numbered years (i.e., 2005, 2007, 2009, 2011 ... down the road)
print in one colour and all even-numbered years in another. That
would be sort of a minimum solution to this. But I didn't find any
code to help in either type of situation in the archives.

See, if I just create conditional formatting for the 3 allowable
conditions, but only actual and specific dates, somewhere down the
road - in this example, in 2008 - user intervention will be required
to fix the coding. Well, they won't do it. Sure, it's highly
unlikely that this workbook will be used for years and years but I
never code or build anything with that as a guide. I make things with
the idea that they can be used "forever", just in case. You just
never know. Even if someone years later only even just adapts the
ideas and code to something else, it'll have served its purpose.
Meanwhile, it'll work for the foreseeable future and I personally have
a use for this calendar with some adaptations "forever". It'll
replace the necessity of my having to buy a date book every year,
something I've had to do every year since 1994! And want to leave it
ready-to-go with code that doesn't need any fixing later one.

Thanks so much!

Merry Christmas

Merry Christmas to you and yours, too! :oD

Getting together with family in early evening, so taking care of
finishing up the gifts this morning. <g

Jason

Ok, It sounded like you didnt mind having to change the formating so
here is a work around. Since we have a max of 3 conditions, I divide
the year (in A1) by 3. Then I used the MOD function to get th
remainder (0, 1 or 2). So we put that formula in each condition.

Condition 1
Formula is =Mod($A$1,3) = 0 'Set your background color

Condition 2
Formula is =Mod($A$1,3) = 1 'Set your background color

Condition 3
Formula is =Mod($A$1,3) = 2 'Set your background color

I think this should work for you. Enjoy your time with the family,

Jaso

--
jt
-----------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...fo&userid=2113
View this thread: http://www.excelforum.com/showthread.php?threadid=49593