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

On Sun, 25 Dec 2005 08:42:20 -0600, jtp
wrote:


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 divided
the year (in A1) by 3. Then I used the MOD function to get the
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

Darn, soooo close! <g

2007 and 2008 have the same colour. 2011 and 2012 also. I'd probably
find the same 3 down the road. I don't care that colours repeat in a
way that is not consistent, it's just that 2 consecutive years as same
colour kinda defeats the purpose! <g

Yet when I remove the third condition, no colour appears in some years
and repeating colours still appear in 2 consecutive years.

Boy, this is going to be a toughie, eh?

Is there anything that can be done?

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

Thank you, likewise! :oD

Jason

Hi StargateFan and Jason,

That's exactly what I meant, I just wasn't sure which cell address to
include in the Mod function.
You can also squeeze in one more color by starting off with an ordinary
fill color with no condition eg yellow, then instead of using
=mod($A$1,3) = 0 then 1 then 2 in each subsequent cond format, use
=mod($A$1,4) = 1 then 2 then 3 in each subsequent cond format. This
way, when mod($A$1,4) = 0 (eg 2008,2012 etc) the cell fill color
defaults to the original fill color before cond format was applied
(yellow) or if you didn't apply one it defaults to no fill, still a
fourth color.

Ken Johnson

Ken, I am sorry. I never saw your post. I sat here for almost 1
minutes trying to figure out what to do and you already posted it.
know I typed it up like that was the first time it had ever bee
suggested so I didn't mean to take any credit from you. It certainl
would help if I opened my eyes from time to time. And your exactl
right about having 4 colors instead of just three! I didn't think o
that.

And StargateFan, it works fine for me. None of the colors repeated an
I went to 2020. Now if you limit it to 2 conditions then your going t
miss an instance. To have 3 with 2 conditions, you need to do it lik
Ken said. Set your header to a particular color. Then setup your mo
to look like Mod(A1,2) = 0 (different color)and Mod(A1,2) = 1 (anothe
different color). However I still dont understand why it isn't workin
for you with the 3 conditions

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

Hi Jason,
I can't understand the problem either, I've had no trouble getting 4
colours, a different one for consecutive years. Maybe I'm not correctly
interpreting StargateFan's description of his/her situation.
Ken Johnson

On 25 Dec 2005 07:44:01 -0800, "Ken Johnson"
wrote:

Hi Jason,
I can't understand the problem either, I've had no trouble getting 4
colours, a different one for consecutive years. Maybe I'm not correctly
interpreting StargateFan's description of his/her situation.
Ken Johnson

Hi, Ken! I didn't get your initial post either until after. I'm
guessing ISPs are running at different speeds <g.

Well, I don't really know why I'm getting repeating colours. Perhaps
if I discuss the steps that will help. It might be clearer. I also
checked and the date is not determined exactly as I'd said <sigh.
Sorry. I think that's what I did initially, but the dates wouldn't
change if the year rolled over so I modified things some weeks after
creating this workbook. Cells A1, C1, E1 and G1, for example,
actually get their year value from a cell below each. These cells are
formatted for yyyy only, though, so they just show the year. I bet
you maybe that this is where the difficulty might lie (?). I'm very
sorry.

This is how it works - when I type in a random date into the message
box, i.e., today's date, A1 shows 2005, A2 shows "Sunday" and B2 shows
"Dec.25.2005". The values of subsequent corresponding cells change
according to previous ones they're dependent just by incrementing by
1.

Although it would be easier all around if the "header" rows change
colour programmatically rather than manually, I've been mulling this
over and perhaps it would be easier if the conditional formatting
changes according to odd or even numbers of the years rather than what
we've tried up till now. Looking at the spreadsheet again this
morning with the 3 conditions taken up and figured that that not only
might make the coding of this easier but would leave one slot free in
case users need to do something that requires conditional formatting
in future.

Sorry for not realizing the full extent of the the issue earlier. I
hope this is all a little clearer?

Thanks so much and esp. on Xmas Day! <lol I won't see my family
till about 6 p.m. so am spending next few hours finishing up some
recordings I'm doing (which leaves me free, unfortunately, to be doing
some work for the office! <sigh<g).

Cheers! :oD

Well after reading this I still don't understand why it doesnt work.

You wrote:
Cells A1, C1, E1 and G1, for example,
actually get their year value from a cell below each. These cells are
formatted for yyyy only, though, so they just show the year. I bet
you maybe that this is where the difficulty might lie (?). I'm very
sorry.

Well if A1 is in the yyyy format (I'm assuming your using the Year(
formula here), it should still work. And we are supposed to change th
header row color by Year, correct? You lost me when you said that yo
bet the problem lies with the yyyy format? Thats what we want! Well
I certainly am no pro at this stuff and I'm sure someone else (fo
instance Ken) can get this solved for you.

Maybe try to MOD() the whole date field like in B2? MOD(YEAR(B2),3)
0 ...

You could also write a routine to do this for you in VBA. Tha
wouldn't take much effort at all. If I wasn't getting off work now,
could write it up really quick but it's been a long night. Good luc
with this.

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

Hi StargateFan,
It's 3:50 am Boxing Day where I am. I'm still struggling with the setup
of your worksheet.
I think the problem is to do with the date formatting. I was getting
different colors for consecutive years because the cell A1 was general
format, so the difference between one year and the next is 1. With yyyy
format I think the difference between consecutive years is 365 or 366
(leap year) which probably stuffs up the simple MOD function.
If you email me a copy of your workbook I can look at it after I've had
a sleep. My brains really struggling right now. I've got to get a
clearer understanding of the sheet setup.
Might find the sheet useful for myself.

Ken Johnson

On 25 Dec 2005 09:16:24 -0800, "Ken Johnson"
wrote:

Hi StargateFan,
It's 3:50 am Boxing Day where I am. I'm still struggling with the setup
of your worksheet.
I think the problem is to do with the date formatting. I was getting
different colors for consecutive years because the cell A1 was general
format, so the difference between one year and the next is 1. With yyyy
format I think the difference between consecutive years is 365 or 366
(leap year) which probably stuffs up the simple MOD function.
If you email me a copy of your workbook I can look at it after I've had
a sleep. My brains really struggling right now. I've got to get a

<g I know the feeling. You were probably running on stubborn and
fumes about that time, eh?

Just getting home from Xmas myself. It's 01h18 Boxing Day up here
<g.

Emailing this to you right after posting this. Thanks!!

clearer understanding of the sheet setup.
Might find the sheet useful for myself.

Sure. Once that part works, it will be really neat in concept. The
principles and coding could be adapted to other uses.

Cheers!

Ken Johnson

Hi StargateFan and Ken,
When you guys figure it out, I'd love to know what was the problem.
I thought about this today in my sleep and I hate that. Just puzzles m
how it doesn't work. Ken I used all different kinds of formats and i
all still worked so there is obviously something I am missing.

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

Hi Jason,
I too am very curious about this worksheet. I've gotta go out right now
but can't wait to get back and have a look. I'll quickly see if I can
send you a copy Jason, you might solve it before me, then you can let
me know.
Ken Johnson

On 26 Dec 2005 00:06:00 -0800, "Ken Johnson"
wrote:

Hi Jason,
I too am very curious about this worksheet. I've gotta go out right now
but can't wait to get back and have a look. I'll quickly see if I can
send you a copy Jason, you might solve it before me, then you can let
me know.

Did you recv okay? I sent the sheet yesterday via hotmail.

Thanks. :oD

Hi StargateFan,
Yes, received OK.
Did you receive my attempted solution sent Dec 27 1:30 AM.
After that I re-applied sheet protection and of course got an error so
I emailed at 2:21 AM about two extra lines of code to stop that
problem.

Ken Johnson

On 27 Dec 2005 14:08:26 -0800, "Ken Johnson"
wrote:

Hi StargateFan,
Yes, received OK.
Did you receive my attempted solution sent Dec 27 1:30 AM.
After that I re-applied sheet protection and of course got an error so
I emailed at 2:21 AM about two extra lines of code to stop that
problem.

Hi, sorry for delay in responding. I'm checking my mail now and will
look at tonight.

Thank you! :oD

On Fri, 30 Dec 2005 07:05:37 -0500, StargateFan
wrote:

On 27 Dec 2005 14:08:26 -0800, "Ken Johnson"
wrote:

Hi StargateFan,
Yes, received OK.
Did you receive my attempted solution sent Dec 27 1:30 AM.
After that I re-applied sheet protection and of course got an error so
I emailed at 2:21 AM about two extra lines of code to stop that
problem.

Hi, sorry for delay in responding. I'm checking my mail now and will
look at tonight.

Thank you! :oD

[I can't remember what the last msg was in this thread (though we did
"speak" in another thread re this solution <g so posting here.]

I've worked with the file several times now, finetuning. It really
works great. And on three separate occasions, I've looked at the code
trying to figure it out. I always just marvel at how something that
looks so simple can accomplish what I was trying to do seemingly so
easy <g.

I was able to figure out one thing, when I added more lines to each
section and made the sheet "longer" then as there were more rows, was
able to figure out that I just needed to change the second value in
the range in the code for it to work under new condition. That was
neat.

Last thing, I just checked something and glad I hadn't yet sent this
post - the line that is depending on the letter "W", when I replaced
that with "Wkt" just to see if it would work, it didn't. Does the
letter 'w' refer to "Wkt" in the sheet, or "Week"? I'm curious,
simply because when it comes time to adapt this to other uses, esp. at
office, would like to know what to change for any given situation.
And I'm afraid I haven't been able to figure that out from in the vbe
<g.

Thanks once again! :oD

Hi Hypatia,
when I replaced that with "Wkt" just to see if it would work, it didn't.<

It does depend on the "Wkt#" and the reason it didn't work was you
probably kept the Number of Characters argument of the "Left" function
equal to 1, so, you have to change that line of code to :

If Left(SheetArray(iRowCounter, iColumnCounter), 3) = "Wkt" Then

for it to work. Notice the 3 ('Wkt" has 3 characters) where previously
there was a 1.

Correct if I'm wrong, if my memory serves me correct, you are not
wanting either the Wkt# or the series of underscores in the following
cell to appear in a new version for use at your workplace.
One way of achieving this is to include a line of code in that version
which makes the font color in those cells the same as the cell interior
color (icolor). This way they're still there, just not visible on
screen or printed page (I assume).

The new code follows:

Public Sub ColorYear()
Dim iRowCounter As Long, iColumnCounter As Long
Dim SheetArray As Variant
SheetArray = Range("A1:H1430")
For iRowCounter = 3 To UBound(SheetArray)
For iColumnCounter = 1 To UBound(SheetArray, 2)

If Left(SheetArray(iRowCounter, iColumnCounter), 1) = "W" Then
iYearIndex = Year(Cells(iRowCounter - 1, iColumnCounter +
1).Value) Mod 4
If iYearIndex = 0 Then Let icolor = 19 '2008,2012,2016
If iYearIndex = 1 Then Let icolor = 40 '2005,2009,2013
If iYearIndex = 2 Then Let icolor = 20 '2006,2010,2014
If iYearIndex = 3 Then Let icolor = 35 '2007,2011,2015

Range(Cells(iRowCounter - 2, iColumnCounter),
Cells(iRowCounter, iColumnCounter + 1)) _
.Interior.ColorIndex = icolor
Range(Cells(iRowCounter, iColumnCounter), Cells(iRowCounter,
iColumnCounter + 1)) _
.Font.ColorIndex = icolor
End If
Next iColumnCounter
Next iRowCounter

End Sub

If you run this code but then you change your mind because you want
them visible again you can change ".Font.ColorIndex = icolor" in the
last line of the loop to ".Font.ColorIndex = 0" then run the code
again.

Let me know how this goes.

Ken Johnson