Auto Sort rows based on a selection from a drop down menu

[mscholnick]

I've created a rows that color code the line with conditional formatting based on the 5 choices in the drop down menu in the "Status" column. I did a custom sort and built a list to put the rows in the desired sequence. Now I want that sort feature to happen automatically based on the Status selected on the drop down. Possible?

 

[Joe4]

Welcome to the Board!

Auto sorting would require the use of VBA. To get the VBA code needed in order to sort your range, you can turn on your Macro Recorder, and record yourself sorting the range manually. Then stop the Macro Recorder, and view the VBA code you just recorded. This will give you a good start on the code the need.

We will then want to drop that VBA code in a "Worksheet_Change" event procedure, which is VBA code that is automatically run whenever a cell is manually updated. In your screen print above, if "Status" is in column G, we will want to watch for changes to column G.

To put this VBA code in the proper place (it MUST be in the sheet module in VBA), simply go to the sheet where your data exists, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code shell in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Exit code if update made is not in column G
    If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub
   
    'Put your sorting code below here
   

End Sub

You can see where you would drop the recorded sorting part of your VBA code.

If you need help getting all of this to work, please copy/paste the recorded sorting VBA code I instructed you on how to get above, and let us know exactly which column the "Status" column is located in, if it is not in column G.

 

[mscholnick]

Thank you! I will give it a try and let you know if I need help with the recorded VBA code.

 

[mscholnick]

I recorded a script for the sort. However, when I run the script, it does not take into account the custom list I created. Below are the script and the custom list. It should sort based on the Status (select using drop down menu) column in the order (PO Received, Hot Prospect, Qualified Prospect Interested Prospect, Prior Hot Prospect. Rather, it sorted as shown below.

I tried recording while building the custom list under custom sort, but got the same result.

Any help greatly appreciated

 

[Joe4]

I do not know anything about the Office Scripts, I only use VBA.
So what I was suggesting is to turn on your VBA Macro Recorder, and record yourself performing the sort steps manually, and then stopping the recorder and viewing the recorded code.