Shading 2 rows on, 2 rows off alternately

[angela2678]

Hello! I run a call center in which we use a spreadsheet to track the employees who've been monitored for quality assurance purposes each month.

I'm creating a new spreadsheet, and I want to use alternate shading. I know how to do that; I do it all the time. But this sheet is different in that I need the pattern to go 2 rows shaded, 2 rows not, etc. I also need it to still be dynamic, because every month I add and remove people from this spreadsheet.

Here is a sample chunk (formatting is not quite in place here, but that's okay):
<TABLE style="WIDTH: 876pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1173><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><TBODY><TR style="HEIGHT: 27pt; mso-height-source: userset" height=36><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6a6a6; WIDTH: 67pt; HEIGHT: 42pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl89 height=56 rowSpan=2 width=89>ID #</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6a6a6; WIDTH: 102pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 rowSpan=2 width=136>Last Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6a6a6; WIDTH: 77pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl90 rowSpan=2 width=102>First Name</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a6a6a6; WIDTH: 630pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl92 width=846 colSpan=18> Monitoring Record</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: #d9d9d9; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl79 height=20 colSpan=3>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl79 colSpan=3>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: gray; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl88 colSpan=3>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: gray; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl88 colSpan=3>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: gray; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: gray 0.5pt solid" class=xl88 colSpan=3>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl79 colSpan=3>6</TD></TR><TR style="HEIGHT: 12.95pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 25.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 height=34 rowSpan=2>3280</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 rowSpan=2>Allen</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl86 rowSpan=2>Mike</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3>

TH​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD></TR><TR style="HEIGHT: 12.95pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.95pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl95 height=17>

7/6​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72>

freq​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73>

3.14​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl95> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD></TR><TR style="HEIGHT: 12.95pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 25.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 height=34 rowSpan=2>3360</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 rowSpan=2>Allison</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl78 rowSpan=2>Cynthia</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3>

VM​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3>

JL​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD></TR><TR style="HEIGHT: 12.95pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.95pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl95 height=17>

7/2​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72>

freq​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73>

3.75​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl95>

7/14​

</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72>rec</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73>3.50</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD></TR><TR style="HEIGHT: 12.95pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 25.9pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 height=34 rowSpan=2>3438</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 rowSpan=2>Baker</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl78 rowSpan=2>Sandra</TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: gray 0.5pt solid" class=xl81 colSpan=3> </TD></TR><TR style="HEIGHT: 12.95pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: gray 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.95pt; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl96 height=17> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl74> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl75> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl95> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl71> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: gray 0.5pt solid; BORDER-LEFT: gray; BACKGROUND-COLOR: transparent; BORDER-TOP: gray; BORDER-RIGHT: gray 0.5pt solid" class=xl73> </TD></TR></TBODY></TABLE>

The QA who does the monitoring puts his initials in the top box, and the details of the monitoring below. So you can see that we have merged cells on the left, and then it splits back into 2 rows on the right.

Ideally, this chunk should be shaded such that the first row on both the left and right sides would be white. The next would be light gray. The third would be white. And so on. But every formula I try can't get it there. I end up with the left side shaded like I want it, but then the right side still goes "every other" and ends up with each person having one white row and one gray.

Let me know if you need any more details.
Any help you can offer would be so appreciated! Thanks!

 

[angela2678]

Also, I'm concerned that any formula that gives me "2 on, 2 off" would also affect the left side. Will those merged rows be counted as 2, or as 1?

 

[VoG]

This may help you http://www.contextures.com/xlCondFormat03.html#Bands

 

[angela2678]

Ah, thank you! That was a big help. It still wouldn't work with the left side merged, but I realize that I can unmerge the left side but remove the borders and have essentially the same effect. So I now have 2 rows on, 2 rows off.

But now a new question: We use a color-coding system on this spreadsheet to note the results of the monitoring session. Green=good, blue=poor, red=very poor, etc. So I want users to be able to color-fill the score cell for each monitoring they enter. When I just tried it for the sample row, it wouldn't shade in rows that are already gray because of the conditional formatting.

Is there a way for me to use the conditional formatting but to have it allow a user to later specify a different fill color?

Thanks again!

 

[angela2678]

I *am* able to add my own color fill to rows that are left white according to the formula.