Banding or Formatting Rows based upon data

[davidcosgrove]

Hello all,
Here is my issue: i have a spreadsheet that varies in size each day. One column has a unique reference number that will refer to 2 or more transactions, we call this the TID field. So as an example, i may have 12 items that are linked as a chain with TID 3033. All i want to do is make sure that TID 3032 and TID 3034 are shaded light blue (or some other color). The TID is always in Column B.
Make sense to anyone?
Thanks
Dave

 

[GlennUK]

Have you looked at Conditional Formatting?

Formula Is,

=OR($B2="TID 3032",$B2="TID 3034")

... assuming that you are selecting A2:X999 ( or whatever range, as long as it starts in row 2, to match the row 2 referenced in the formula ).

 

[davidcosgrove]

GlennUK
Thank you for the response. Yes i am struggling to accomplish this through CF or VBA. I am trying to get it to work properly. CF is tough because of the varied number of rows. I am trying to find a VBA solution.
Thank you again.

 

[GlennUK]

Maybe someone can help ... what did you mean by "varied number of rows" causing a problem?

 

[davidcosgrove]

I am sure i am the problem in this equation. the varied rows meant that sometimes the Tid will be in 2 rows, other times N rows. as an example today i have a sample of:
<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=70 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=70 height=17>5041</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>5041</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>5033</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>5033</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>5033</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1292</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1292</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1292</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1291</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1291</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1291</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1290</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1290</TD></TR></TBODY></TABLE>
I would like to have 5041 (2 rows) shaded light blue, 5033 (3 rows) no shading. 1292 (3 rows) shaded light blue, 1291 (3 rows) no shading, 1290 light blue....
Thank you very much.
Dave

 

[GlennUK]

Is it that you want any TID group shaded light blue, regardless of the actual TID numbers, as long as the shading is for alternate groups? ( block 1, then block 3, then block 5 etc etc )

 

[davidcosgrove]

yes, i believe that would be correct as well.
Thank you again.

 

[GlennUK]

A condition of:

=MOD(SUM(1/COUNTIF($B$2:$B2,$B$2:$B2)),2)=1

with formatting light blue should do it.

That is assuming that you are selecting A2:X999 ( or whatever range, as long as it starts in row 2, to match the row 2 referenced in the formula ).

 

[davidcosgrove]

Glenn,
Thanks for the extra push, that worked. I then noticed that there instances where the banding would stop. Appears that there are rounding errors in XL2007 so i applied ROUND to your formual.
Thanks again so much for your help.
Dave

 

[GlennUK]

That's great. I'm glad you were able to tweak it for an improved result