Color Banding a range

[RLJ]

I need code to put a color band on a range of rows. In Column B I have numbers and each new number, I need a color band on the row. However sometimes the numbers repeat and need the range of rows color banded. For the Eample below, Rows 16:17 & 19 & 21:22, etc color banded

Ins Exp - 2012-02


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center">28000110</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">33000058</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">33000058</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: center">33000061</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">40000032</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: center">49000003</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">49000022</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">49000022</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: center">49000023</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: center">49000023</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">63000060</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c0c0c0">63000060</TD></TR></TBODY></TABLE>
Thanks for your help

 

[T. Valko]

I need code to put a color band on a range of rows. In Column B I have numbers and each new number, I need a color band on the row. However sometimes the numbers repeat and need the range of rows color banded. For the Eample below, Rows 16:17 & 19 & 21:22, etc color banded

Ins Exp - 2012-02


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">28000110</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">33000058</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">33000058</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: center">33000061</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">40000032</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">49000003</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">49000022</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">49000022</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">49000023</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">49000023</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">63000060</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">63000060</TD></TR></TBODY></TABLE>
Thanks for your help

This can be done using conditional formatting or do you want a VBA solution?

 

[RLJ]

Conditional Formatting witll be great.

 

[Tom Urtis]

See if this example helps.

 

[T. Valko]

Conditional Formatting witll be great.

Try this...

Book1
	B
15	28000110
16	33000058
17	33000058
18	33000061
19	40000032
20	49000003
21	49000022
22	49000022
23	49000023
24	49000023
25	63000060
26	63000060
Sheet1

There must be a cell above the first cell to be formatted and this cell must not contain the same entry as the first cell in the range to be formatted.

Select the entire range to be formatted B15:B26.
Conditional Formatting
Use the "formula option"
Use this formula:

=MOD(SUMPRODUCT(--(B$14:B14<>B$15:B15)),2)=0

Set the desired fil color
OK out

This will slow things down if you need to format 1000's of rows of data.

 

[TBP]

Try this...

Sheet1

	B
15	28000110
16	33000058
17	33000058
18	33000061
19	40000032
20	49000003
21	49000022
22	49000022
23	49000023
24	49000023
25	63000060
26	63000060

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"></colgroup><tbody>
</tbody>

There must be a cell above the first cell to be formatted and this cell must not contain the same entry as the first cell in the range to be formatted.

Select the entire range to be formatted B15:B26.
Conditional Formatting
Use the "formula option"
Use this formula:

=MOD(SUMPRODUCT(--(B$14:B14<>B$15:B15)),2)=0

Set the desired fil color
OK out

This will slow things down if you need to format 1000's of rows of data.

Hi Biff,
I can get this to work perfectlly in a single range B15:B26 but how do I get it to work for say A15:F26?
Thanks and Merry Christmas

 

[mashhood]

Try this

select your data that is range A15:f26

then go to conditional formatting using formula and type the formulla

=COUNTIF($B$14:$B$26,$B2)>1

Selet which ever color you like.

Thanks

 

[TBP]

Try this

select your data that is range A15:f26

then go to conditional formatting using formula and type the formulla

=COUNTIF($B$14:$B$26,$B2)>1

Selet which ever color you like.

Thanks

With the below i get no fill

=COUNTIF($B$14:$B$26,$B2)>1

and with the below I get fill but not working correctly

=COUNTIF($B$14:$B$26,$B14)>1

Thanks

 

[mashhood]

thanks for pointing the mistake. . . do u want to highlight duplicates in A15:F26 ?? each column at a time?? please explain a bit more

 

[mashhood]

i dont know your exact scenario but try using =COUNTIF(B$14:B$26,B14)>1