Change the colour of rows when the value in the first column changes

[chan_a1]

Hi

Wondering if someone can help me with this query. I have a spreadsheet of account numbers and customer Name. I would like the rows to change colour when there is a change in the account numbers.

Eg the first 2 columns would be like this

Account Number Customer Name
<table width="229" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 124pt;" width="165"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt; width: 48pt;" width="64" align="right" height="17">100241</td> <td class="xl69" style="width: 124pt;" width="165"> Mr Smith</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" align="right" height="17">100241</td> <td class="xl69"> Mr Richard </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" align="right" height="17">100283</td> <td class="xl70"> Cuthbertson</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" align="right" height="17">100302</td> <td class="xl69"> Varnor Pty Ltd</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" align="right" height="17">100302</td> <td class="xl69"> Varnor Pty Ltd</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" align="right" height="17">100334</td> <td class="xl70"> John & Thompson</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl68" style="height: 12.75pt;" align="right" height="17">100372</td> <td class="xl69"> Mr Mark K</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" align="right" height="17">100419</td> <td class="xl70"> Hungry Minds
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl67" style="height: 12.75pt;" align="right" height="17">100419</td> <td class="xl70"> Hungry Minds
</td> </tr> </tbody></table>
When there is a change in the account number i want the the row to be coloured differently. Eg row 1 and 2 would be coloured blue, row 3 would be coloured green, row 4 and 5 coloured blue, row 6 will be green...etc

Appreciate an assistance with this

 

[Akashwani]

Hi,

Do you mean like this?....

Excel Workbook
	A	B
1	Account Number	Customer Name
2	100241	Mr Smith
3	100241	Mr Richard
4	100283	Cuthbertson
5	100302	Varnor Pty Ltd
6	100302	Varnor Pty Ltd
7	100334	John & Thompson
8	100372	Mr Mark K
9	100419	Hungry Minds
10	100419	Hungry Minds
Sheet3

In 2007, Highlight the range from A2:B10, Go to Conditional Formatting, New Rule, Use a formula etc, and paste this into the box.... =Countif($A$2:$A$10,$A2)>1
Select the Format that you require (blue), click Ok, Ok, OK.
Whilst you have your range highlighted select the fill colour (green) from the Home tab, you should now have what you require.

I hope that helps.

Ak