Autofomrat Group of rows with same cell value

sanketgroup

New member
Joined
Oct 8, 2011
Messages
2
Reaction score
0
Points
0
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
 

Attachments

  • excel format.jpg
    excel format.jpg
    98.3 KB · Views: 36
  • macro auto format.xlsx
    17.5 KB · Views: 10

joseph4tw

New member
Joined
May 28, 2012
Messages
13
Reaction score
0
Points
0
Location
South Florida, USA
Website
www.spreadsheetsmadeeasy.com
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:

Code:
=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
    Code:
    =MOD($A1,2)=0
  5. Apply a light blue
  6. OK/Apply
  7. Add new rule
  8. Use a formula to determine...
  9. Use
    Code:
    =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 View attachment macro auto format.xlsx
 
Top