XL: Sample Macro to Sort List Based on Custom Sort Order

MORE INFORMATION

1. In a new worksheet, type the following data:

         A1: red
         A2: blue
         A3: green
         A4: yellow
         A5: pink
   					

2. On the Tools menu, click Options (or Preferences if you are using Excel for the Macintosh), and in the Options (or Preferences) dialog box click the Custom Lists tab.
3. In the Import list from cells box, type A1:A5, and then click Import.
   
   You should now have a custom sort list based on the list in step 1.
4. Click OK.
5. Type the following data in a new worksheet. (This data will be the data that is sorted by the sample macro.)

         A1: pink
         A2: blue
         A3: green
         A4: blue
         A5: yellow
         A6: pink
         A7: red
         A8: blue
         A9: red
   					

6. In a new module sheet, type the following macro code:

         Sub Custom_Sort()
            Range("A1").Sort Key1:=Range("A1"), Order1:= _
               xlAscending, Header:=xlGuess, OrderCustom:=6, _
               MatchCase:=False, Orientation:= xlTopToBottom
         End Sub
   						

   NOTE: The number for the "OrderCustom" argument is the position of your custom list in the list from the Custom Lists tab of the Options dialog box. The number you use may be different from the one used in this example. To find out what number you should use, click Options on the Tools menu, and click the Custom Lists tab. By default, there are four default sort lists in Microsoft Excel. Their values for the "OrderCustom" argument would be 2, 3, 4, and 5, respectively. Therefore, the first custom sort list would be 6. If you have only the custom sort list created in steps 1 through 3 above, this is the value you would use for the "OrderCustom" argument. Use this value because the number 1 item in the list is reserved for the "New List" item.
7. Switch to the worksheet where you entered the value in step 4, and then run the Custom_Sort macro.
   
   The list should be sorted according to the custom sort list you created in steps 1-3, and your data should resemble the following:

         A1: red
         A2: red
         A3: blue
         A4: blue
         A5: blue
         A6: green
         A7: yellow
         A8: pink
         A9: pink