Color Banding a range

R

RLJ

Active Member
Joined
Mar 15, 2011
Messages
417
Office Version
  1. 365
Platform
  1. Windows
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
 
Sort by date Sort by votes
No luck with =COUNTIF(B$14:B$26,B14)>1
No luck with Biffs either =MOD(SUMPRODUCT(--(B$14:B14<>B$15:B15)),2)=0
This is the result im after (i had to use font color as cell color dissapeared when I pasted in) with it conditionally formatting entire rows based on column B.
I select data A15:F26 then conditional format using formula and then select my fill color.

ABCDEF
150.989251280001100.3288880.2983970.4723180.084304
160.675099
330000580.3386550.0798970.0844670.866417
170.409044
330000580.0403280.3724340.8148820.058516
180.366147330000610.8300190.0523570.0644470.860275
190.885004
400000320.946240.5755750.1245630.144847
200.631058490000030.4863710.2917210.7036680.455342
210.505697
490000220.2873730.5566040.900010.395686
220.068672
490000220.8459590.7965670.5792320.353596
230.155163490000230.6908870.7469610.5369260.627731
240.798903490000230.6437520.8700320.0948630.602132
250.513196
630000600.5429330.5728260.9535340.708152
260.898238630000600.6291970.049610.2239440.100775

<tbody>
</tbody>
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
TBP said:
No luck with =COUNTIF(B$14:B$26,B14)>1
No luck with Biffs either =MOD(SUMPRODUCT(--(B$14:B14<>B$15:B15)),2)=0
This is the result im after (i had to use font color as cell color dissapeared when I pasted in) with it conditionally formatting entire rows based on column B.
I select data A15:F26 then conditional format using formula and then select my fill color.

ABCDEF
150.989251280001100.3288880.2983970.4723180.084304
160.675099
330000580.3386550.0798970.0844670.866417
170.409044
330000580.0403280.3724340.8148820.058516
180.366147330000610.8300190.0523570.0644470.860275
190.885004
400000320.946240.5755750.1245630.144847
200.631058490000030.4863710.2917210.7036680.455342
210.505697
490000220.2873730.5566040.900010.395686
220.068672
490000220.8459590.7965670.5792320.353596
230.155163490000230.6908870.7469610.5369260.627731
240.798903490000230.6437520.8700320.0948630.602132
250.513196
630000600.5429330.5728260.9535340.708152
260.898238630000600.6291970.049610.2239440.100775

<tbody>
</tbody>
Click to expand...

You have six columns of data, but presented your problem as if you only had one... no wonder none of the formulas did not work. Try this (using a modification to Biff's formula to account for the additional columns). For what you showed us above, select the range A15:F26 and make sure you start the selection with cell A15 (it must be the active cell in the selection in order for the formula to work) and then use this formula...

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

Then, of course, set your red text format and then OK out of the dialog. The cells should now be colored as you wanted.
 
Upvote 0
try this

=COUNTIF($B$15:$B$26,$B15)>1

this is working for me make sure you copy the $ sign in exactly the same position. ;)
 
Upvote 0
Rick Rothstein said:
You have six columns of data, but presented your problem as if you only had one... no wonder none of the formulas did not work. Try this (using a modification to Biff's formula to account for the additional columns). For what you showed us above, select the range A15:F26 and make sure you start the selection with cell A15 (it must be the active cell in the selection in order for the formula to work) and then use this formula...

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

Then, of course, set your red text format and then OK out of the dialog. The cells should now be colored as you wanted.
Click to expand...

Hi Rick I probably should have started a new thread but my question was so close to the original poster I thought I could just continue on and change it a bit.

The formula works perfectly Thanks
 
Upvote 0
mashhood said:
try this

=COUNTIF($B$15:$B$26,$B15)>1

this is working for me make sure you copy the $ sign in exactly the same position. ;)
Click to expand...

mashhood I could not get your formula to work (see below) only worked for first change, maybe I didn’t explain my problem properly but Rick has helped me with a solution.
Thanks for your time.

ABCDEF
150.989251280001100.3288880.2983970.4723180.084304
160.675099
330000580.3386550.0798970.0844670.866417
170.409044
330000580.0403280.3724340.8148820.058516
180.366147330000610.8300190.0523570.0644470.860275
190.885004400000320.946240.5755750.1245630.144847
200.631058490000030.4863710.2917210.7036680.455342
210.505697
490000220.2873730.5566040.900010.395686
220.068672
490000220.8459590.7965670.5792320.353596
230.155163
49000023
0.6908870.7469610.5369260.627731
240.798903
490000230.6437520.8700320.0948630.602132
250.513196
630000600.5429330.5728260.9535340.708152
260.898238630000600.6291970.049610.2239440.100775

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
460
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
363
esben
E
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
864
gvsumich
G
V
  • Question
Issue with scraping with VBA
Replies
1
Views
536
John_w
J

Forum statistics

Threads
1,216,029
Messages
6,128,403
Members
449,448
Latest member
Andrew Slatter

Share this page

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
Back
Top