Related to Thread: Colour Entire Row Base on the Value of Two Other Cells in Excel

[mexmike]

Hello All,
SamT has mentioned that he’ll be away for a while due to PC problems and that there might be help available from another kind VBA Guru!!!!

Please have a gander at the project SamT has been helping me with regarding colouring fonts depending on cell value.

Ok, for the most part, I’ve got things working; maybe not exactly to SamT’s spec but it’s working.

If you look at each sheet (Sorry for the repetitive garbage data), you’ll see the fonts are colour coded according to priority criteria from the highest… Red, Orange, Green, Blue to the lowest… Black, some of which have two criteria for time, running hours and calendar days. The highest priority tasks and next tasks are:- Red for no time remaining or overdue, and:- Orange for tasks requiring immediate completion. You can sort by column heading. Normally, I would choose hours or days remaining in order to view the next tasks due. My live working project uses macros for sorting, not the current filtering as in this sample project.

SamT also helped me to fill one cell in each row in column “D” with the same colour as the font of the highest priority colour of the two criteria by which my task are governed (hours or days).

Finally and to get to the point of all this hot air. I’m trying to order tasks in each row by colour. If you click the Sort by Colour button over column “D”, the tasks, both hour and day orientated will be ordered by their colour priority, bringing all task of the same colour into one region of the each sheet. This allows me to see all items within a particular criteria in the same place on each sheet. You will notice, however, that even though tasks of the same colour sort into the same area on the sheet, they themselves are not necessarily ordered correctly within each colour band.

My intention is to have the Sort by Colour button, sort the tasks by hours or days chronologically within each colour band. (I've use a custom sort using color index numbers).

Sorry for the shaggy dog story!
Please see attached workbook and code snippet below and many thanks for getting involved with my baby.

Sub MacroColorSort()

        Dim iCounter As Integer
   Application.ScreenUpdating = False
   
   
   For iCounter = 11 To 139
      Cells(iCounter, "P") = _
         Cells(iCounter, 4).Interior.ColorIndex
         
   Next iCounter
   
    Range("A11:O139").Select
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("P11:P139"), CustomOrder:="3,45,50,5,1,-4142,2"
        
    With ActiveSheet.Sort
        .SetRange Range("A11:P139")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
   
     Columns("P").ClearContents
   Application.ScreenUpdating = True
   Range("D9").Select
   
  
End Sub

[mexmike]

Sorry looks like I forgot to upload the workbook in my previous. Please see attached