Help with Macro to Automatically Sort Excel Sheet by column with colors as data entered

Question

I have an excel sheet that I created that is like a task list. I created a column which allows for me to enter the Status.... It has conditional formatting which will change it to a color assigned to the word for that status. I also created a custom sort which will sort them where all the "in progress" goes to the top, then "waiting on someone" then "haven't started" etc....

Question: I would like them to automatically sort by that custom sort as I fill out the data, so i don't have to keep doing the custom sort each time.

Screenshot attached.

Answer 1

Do you really need the data to be sorted as they are entered? It's rather disconcerting when a row moves out of sight the moment you enter a status.

I'd prefer to have the sheet be sorted when it is activated.

Answer 2

I don't need it to but really the status is usually the last part I enter... the other columns don't always get filled in as they are optional. I guess I would just want to have the option for it to automatically sort and if I found it annoying I would undo it.

I don't even know if its possible.

Answer 3

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

You'll have to allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2:B" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Header:=xlYes, _
            OrderCustom:=Application.CustomListCount
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Remark: this code assumes that the custom list "In Progress, ..." is the last one in the list of custom sort orders (File > Options > Advanced > Custom Lists...). If it's not the last one, replace Application.CustomListCount with the index number of the one you want to use in that list.

Answer 4

Is there a way to do this so it will custom sort but in a custom order? I re-wrote your code for a similar item I am trying to accomplish from the above. It worked but it is sorting based on alphabetical order and I want it to be a custom order.