Conditional Formatting by Date

[Bob]

I have a spreadsheet with dates in the first column. I'd like to use
conditional formatting to alternate shading each time the date changes. For
example:
The rows with 3/23/04 in the first column would be shaded gray,
The rows with 3/24/04 in the first column would be white,
The rows with 3/25/04 in the first column would be shaded gray,
etc.

I can get the first row to change color by highliting the cells and using:
Formula is =A2 <> A1 (Set to gray)
Formula is =A2 = A1 (Set to white)
but this doesn't work for the rest of the cells with the same date.

Any ideas?

Thanks
Bob
rhw => lucent com

 

[Bob Phillips]

Bob,

I think this is what you mean

Formula is =A$2 <> A1 (Set to gray)
Formula is =A$2 = A1 (Set to white)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob]

Bob,
Yes sorry, that's actuallly what I have.

The problem is if there is more than 1 row with the same date then only the
first row gets shaded gray since the remainder of the rows see the 2nd
condition as true and end up being white. That's actually the problem I'm
trying to figure out.

Thanks,

Bob W.
==============================

Bob,

I think this is what you mean

Formula is =A$2 <> A1 (Set to gray)
Formula is =A$2 = A1 (Set to white)

....

 

[Charlie]

See Chip Pearson's page on color banding.
http://www.cpearson.com/excel/banding.htm

Charlie O'Neill

 

[Bob Phillips]

In my test, all of the rows without a date of 23 Mar are gray. The actual
formula should be

=$A$2<>A1
and the second formula is superfluous.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[amyh]

I am having similar issues. I have read Pearson's post on conditiona
color banding, but it looks like it accomplishes only odd or eve
banding. If you have a condition of changing dates (or in my case
changing numbers) then we need a different solution. You may have
lines with the same date before it needs to change colors

 

[David McRitchie]

I don't see your original question or replies to it, I could
check Google Groups archives but I think this will do.

Perhaps my page on Conditional formatting would help
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Chip Pearson has more than one page on Conditional Formatting
You can check his Topic Index
http://www.cpearson.com/excel/topic.htm

 

[amyh]

Thanks David McRitchie! You had sent me a link to your conditiona
formatting page and I got it to work