Autofomrat Group of rows with same cell value

[sanketgroup]

Hi

Column A contains account numbers, and other columns contains some transactions related to account.

30552-1 GARCIA, NORMA 8/18/2011 20550
30552-1 GARCIA, NORMA 8/18/2011 J1040
30552-1 GARCIA, NORMA 8/18/2011 J2010
47365-1 HARDEN, BOWDY 2/8/2011 99213
47365-1 HARDEN, BOWDY 2/8/2011 73110
24240-1 HENRY, PAUL G 8/30/2011 97110
24240-1 HENRY, PAUL G 8/30/2011 97140
48247-1 HILL, RITA A 8/19/2011 99244
4798-1 HUSSETT, DEVON L 8/30/2011 97110
4798-1 HUSSETT, DEVON L 8/30/2011 97140
4798-1 HUSSETT, DEVON L 8/16/2011 97110

I want macro to select group with same account number and then format it with alternate colors band and border.

I have attached screen shot as well excel sheet

[joseph4tw]

Hello,

How about conditional formatting?

First, I would add a new, hidden column to display the count of unique values "so far" going down the list.

So, if I put the data in B1, then in A1 I would have:

=ROUND(SUMPRODUCT(1/COUNTIF($B$1:$B1,$B$1:$B1)),0)

Then copy down to the end.

The numbers will increase with every new unique value it finds in the list as the formula is copied downward.

You can then create a conditional format formula based on this info.

1. Select B1:E44
2. Conditional Formatting -> New Rule...
3. Use a formula to determine which cells to format
4. Use

   =MOD($A1,2)=0

5. Apply a light blue
6. OK/Apply
7. Add new rule
8. Use a formula to determine...
9. Use

   =MOD($A1,2)<>0

10. Apply a darker blue
11. Hide column A

If you can live without the borders, it will work pretty well for you and you won't have to run a macro every time. I hope this helps.

Attached is an example macro auto format.xlsx