Format rows according to the value of a column

benignor

New Member
Joined
Aug 22, 2007
Messages
6
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

benignor

New Member
Joined
Aug 22, 2007
Messages
6
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

Board Regular
Joined
Dec 3, 2006
Messages
164
Office Version
  1. 365
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

New Member
Joined
Aug 22, 2007
Messages
6

ADVERTISEMENT

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

Board Regular
Joined
Dec 3, 2006
Messages
164
Office Version
  1. 365
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

New Member
Joined
Aug 22, 2007
Messages
6

ADVERTISEMENT

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.

formattingzx8.jpg
 

benignor

New Member
Joined
Aug 22, 2007
Messages
6
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

Board Regular
Joined
Dec 3, 2006
Messages
164
Office Version
  1. 365
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

New Member
Joined
Aug 22, 2007
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,488
Messages
5,601,975
Members
414,487
Latest member
inxlsplot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top