Results 1 to 2 of 2

Thread: Autofomrat Group of rows with same cell value

  1. #1

    Question Autofomrat Group of rows with same cell value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	excel format.jpg 
Views:	31 
Size:	98.3 KB 
ID:	1152  
    Attached Files Attached Files

  2. #2
    Seeker joseph4tw's Avatar
    Join Date
    May 2012
    Location
    South Florida, USA
    Posts
    13
    Articles
    0
    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 macro auto format.xlsx

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •