Formatting series of rows according to column value

**benignor** · 08-23-2007, 11:14 AM

Hi,

I have a large datset that looks like this:

++++++++++++++++++++++++++++++++++
+ + A + B + C +
++++++++++++++++++++++++++++++++++
+1+ ID + EventDate + EventType +
+2+ 111 + 1/1/1995 + Visit +
+3+ 111 + 1/30/1995 + Vaccine +
+4+ 111 + 4/22/1998 + Visit +
+5+ 222 + 7/7/2000 + Survey +
+6+ 333 + 8/8/2001 + Labs +
+7+ 333 + 8/22/07 + Pharmacy +
+8+ 333 + 8/22/07 + Visit +

I would like to shade the rows with alternating colors so that all the data pertaining to a given ID can be easily spotted. In the example above, I would need A2:C4 to be shaded one color, A5:C5 another color, and A6:C8 the first color again.

How can this be done? I am using Excel 2007.

Thanks much.

**NBVC** · 08-23-2007, 11:38 AM

See here:

http://www.mvps.org/dmcritchie/excel/condfmt.htm

Look for the heading: Color Grouping with alternating colors (#grouping)

**benignor** · 08-23-2007, 11:45 AM

Thnks for the useful link.

Sadly, I tried it, and I get the "helper column" right, but when I actually apply the CF (formula =$E1=1), nothing happens! Could it be that there is a slightly different approach in Excel 2007?

**NBVC** · 08-23-2007, 11:51 AM

I don't have 2007, so I am not exactly sure...but I wouldn't imagine that it would be different.

Make sure your referencing the right cell when applying the CF formula....it should reference the top-most cell in your selection (your helper column).

If you save your file as an XL2003 and zip it and post it here, I can look at it and you can see if it will be the same again when you re-save it as 2007.

**benignor** · 08-23-2007, 12:13 PM

Huh! So, I tried to save it as 2003, and I got a message that says "Significant loss of functionality. Some cells contain conditional formatting with the 'Stop if true' condition cleared. Earlier versions of Excel do not recognize this option and will stop after the first true condition".

Does this mean somtehing to anybody? Anyway, will ignore it, save it as 2003, and ship it to the list momentarily.

**benignor** · 08-23-2007, 12:24 PM

OK, here it is. Thank you for offering to take a look.

**NBVC** · 08-23-2007, 01:09 PM

Ok.

A couple of things that I noticed...

1. You were referencing E1 when your formula is in H1....

2. You had quotations around the formula. Sometimes Excel adds those...you have to double-check that.

See attached...hopefully it uploads to 2007 ok.

Note: You can hide column H, so that it is not viewable, but still functional.

**benignor** · 08-23-2007, 01:16 PM

Absolutely beautiful! Works like a charm! I had caught the E --> H column issue, changed it, and that didn't fix it. So, all you did was remove the quotes? Or did you modify it in any other way?

I didn't enter those quotes manually, so XL must have inserted them at some point.

Thank you so much. This is exactly what I needed.

**NBVC** · 08-23-2007, 01:19 PM

Originally Posted by benignor

Absolutely beautiful! Works like a charm! I had caught the E --> H column issue, changed it, and that didn't fix it. So, all you did was remove the quotes? Or did you modify it in any other way?

I didn't enter those quotes manually, so XL must have inserted them at some point.

Thank you so much. This is exactly what I needed.

I am happy it worked.

Besides changing the E to an H, yes all I did was remove the quotes.

**benignor** · 08-23-2007, 01:26 PM

OK, I'm new to the forum, so am not familiar with the etiquette. Anything I need to do in order to declare this thread closed?

**NBVC** · 08-23-2007, 01:29 PM

Originally Posted by benignor

OK, I'm new to the forum, so am not familiar with the etiquette. Anything I need to do in order to declare this thread closed?

Not really, I don't think. You could add the word "Solved" to the title or your last post in the thread could indicate the fact the problem was solved to your satisfaction....