Format rows according to the value of a column

[benignor]

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 give 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.

 

[dakota727]

I think this site will illustrate what you want...

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

under the color grouping section.

 

[benignor]

Thanks,

Tried this approach, but did not work as expected - all values in the "helper" column became 1, except for the first cell, which I forced to be 0.
I eliminated the OR operator from the formula, since my identifier is contained in a single column, not 2 as in the example. Any ideas?

 

[dakota727]

I used your data layout (with cell A2 being the first line of data 111) and copied the following into the cells:
E1 = 0
E2 = MOD(OFFSET($E2,-1,0)+ ($A2<>OFFSET($A2,-1,0)),2)

Then I copied the formula from E2 down. Column E is working as I expected, toggling back and forth between 1 and 0. Is the above formula what you are using?

I'm using XL2003.

Let me know if that helps.

 

[benignor]

So close! Thanks, it did work this time (it was my mistake, I had one of my references wrong). I have a functioning "helper" column.

But I still canot get the final result right. I select cell E1, hit Ctrl+A (my data range gets selected), then go to CF and enter =$E1=1 for the formula, and nothing happens! Waht am I still doing wrong?

 

[dakota727]

After you highlight your data set and go to condition 1 and change it to "formula is" in the drop down. Then set the value to =$E1=1 Then click the format button that is right next to it and select the pattern tab. Pick a color and click Ok and OK.

That should do it. I'm not at a mchine that has xl2007 so it may be a little different. But that should be it. If it still has problems let me know and I'll go get on XL2007 and be more specific.

 

[benignor]

So, the fastest way to get to the same place in 2007 (as best I can tell) is Home ribbon > Conditional Formatting > Manage rules > New Rule > Use a formula to select which cells to format. This is how I entered it initially. Enclosed is a screenshot of waht it looks like. Do yyou see anything out of place?

BTW, I tried with both "Stop if true" checked and unchecked, but no dice.

Thank you again for taking the time to help.

 

[benignor]

Sorry, me again. Yes I do see something out of place. But I just fixed the reference (again!) to H1 instead of E1, and still no result.

 

[dakota727]

Since I do not have xl2007 at home, If you don't have it figured out by the morning I can take a look when I get back to work.

Until then try this:
Remove the CF of the entire area. Then just highlight row two and put in the formula "=$h2=1" and apply it only two the single row. If that shades correctly then copy that row and paste special formats to the rest of your spreadsheet.

If that doesn't work maybe someone with xl2007 can give you the exact steps needed.

 

[benignor]

SOLVED!

I just got it to work. The problem were the quotes around the formula, which XL apprently inserted there for some reason (="$H1=1", as opposed to =$H1=1.) The quotes gone, it works like a charm!

Many thanks for all the help.