# Sort values based on colors, then on values using VBA code



## Saher Naji (Sunday at 1:28 PM)

Hello, I'm tryin to sort values based on colors, then on values

The macro was working very well, but on one sheet, so I have to create a new module for each sheet, and because I have around 400 sheets,

This is the simple working macro:

```
Sub A_Sort()
'
' A_Sort Macro
'

'
    Range("B4:J43").Select
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
        208, 142)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
        176, 132)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
        137, 219)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
        194, 230)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_3").Sort
        .SetRange Range("B3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4:B43").Select
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add2 Key:=Range("B4:B43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_3").Sort
        .SetRange Range("B4:B43")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C4").Select
End Sub
```

The best way is to run the macro for the active sheet, not for a named sheet

This my try, but it's not working, I don't know how to re-write the code to work on the active sheet


```
Sub A_Sort()
'
' A_Sort Macro
'

'
    Dim WS As Worksheet

    Set WS = ActiveSheet

    With WS.Sort
    Range("B4:J43").Select
    WS.Range("G3:G43").Sort.SortFields.Clear
    WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
        208, 142)
    WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
        176, 132)
   WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
        137, 219)
    WS.Range("G3:G43")).Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
        194, 230)
   WS.Range("G3:G43").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS.Range("G3:G43").Sort
        .SetRange Range("B3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4:B43").Select
    WS.Range("G3:G43").Sort.SortFields.Clear
   WS.Range("G3:G43").Sort.SortFields.Add2 Key:=Range("B4:B43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS.Range("G3:G43").Sort
        .SetRange Range("B4:B43")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C4").Select
End Sub
```


Thank you very much


----------



## breynolds0431 (Sunday at 1:46 PM)

Hi there. Give this worksheet loop a try. 


```
Sub A_Sort()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    With ws
        .Range("B4:J43").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
            208, 142)
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
            176, 132)
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
            137, 219)
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
            194, 230)
        .Sort.SortFields.Add2 Key:=Range("G4:G43") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("B3:J43")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("B4:B43").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=Range("B4:B43") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("B4:B43")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("C4").Select
    End With
Next ws

End Sub
```


----------



## Saher Naji (Sunday at 1:51 PM)

Hi @breynolds0431 
Thanks, I think it needs more adjustment to work


----------



## Saher Naji (Sunday at 1:54 PM)

I have another VBA code, and it's working very well, but for another purpose
Take a look please


```
Sub SortActiveSheet()
    Dim ws As Worksheet

    Set ws = ActiveSheet

    With ws.Sort
        If Not ws.Range("C4:J43").MergeCells Then
            .SortFields.Clear
            .SortFields.Add2 Key:=ws.Range("G3:G43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange ws.Range("C3:J43")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        Else
            MsgBox "Error - sort range contains merged cells", vbCritical, "Sorting worksheet: " & ws.Name
        End If
    End With
End Sub
```


----------



## breynolds0431 (Sunday at 2:01 PM)

Saher Naji said:


> Hi @breynolds0431
> Thanks, I think it needs more adjustment to work
> 
> View attachment 82311


Oh, sorry. Since you are selecting cells, which usually isn't necessary, you'd also need to select the sheet as you loop through them. Try the below. 


```
Sub A_Sort()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

    With ws
        .Select
        .Range("B4:J43").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
            208, 142)
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
            176, 132)
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
            137, 219)
        .Sort.SortFields.Add(Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
            194, 230)
        .Sort.SortFields.Add2 Key:=Range("G4:G43") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("B3:J43")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("B4:B43").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=Range("B4:B43") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("B4:B43")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("C4").Select
    End With
Next ws

End Sub
```


----------



## Saher Naji (Sunday at 2:11 PM)

Thanks for your cooperation
I faced another issue


----------



## breynolds0431 (Sunday at 7:30 PM)

Would you be able to provide a little example, preferably using XL2BB or sharing on dropbox, of one of the sheets to format? If you want to just provide a template/shell without any sensitive data, that should be okay. Just trying to better understand what needs to be sorted on each sheet.


----------



## johnnyL (Sunday at 10:49 PM)

Doesn't:


```
ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(169, 208, 142)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(244, 176, 132)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(184, 137, 219)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(155, 194, 230)
```

Just Equate to:

```
ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), xlSortOnCellColor, xlAscending, _
            , xlSortNormal).SortOnValue.Color = RGB(155, 194, 230)
```
?


----------



## Saher Naji (Sunday at 11:45 PM)

breynolds0431 said:


> Would you be able to provide a little example, preferably using XL2BB or sharing on dropbox, of one of the sheets to format? If you want to just provide a template/shell without any sensitive data, that should be okay. Just trying to better understand what needs to be sorted on each sheet.


test it.xlsmABCDEFGHIJK12                     SPAIN GMT+1  //   BRAZIL GMT-3              M 04:00 - 10:00   |  A 10:00 - 16:00  |   E 16:00 - 22:00  |   N 22:00 - 04:003FLIGHT #TRAVELLERFROMTOAIRLINETIMESTATUSCOMMENTS40131. RebeccaBCNEZEIB0:30→DATE CHANGED87.00 €50247. RickMXPVIEOS9:15OK60348. MarkMXPVIEOS9:15OK70447. DaleVIEKIVOS11:40OK80548. SmithVIEKIVOS11:40OK90606. JohnJFKPUJB614:49OK100790. WilliamCPTLHRBA16:40→REFUNDED110803. SandyEZELHRBA18:25→REFUNDED120903. ClaudeAEPGRUG322:35OK131084. MichaleSJOMADIB23:50OK14111512161317141815191620172118221923202421252226232724282529263027312832293330343135323633373438353936403741384239434044  TOTALS        BUMPEDUSEDBOOKINGS450710Jan_4Cell FormulasRangeFormulaF45F45=COUNTIF(H4:H43,"X")H45H45=COUNTIF(H4:H43,"OK")I45I45=COUNTA(F4:F43)Cells with Conditional FormattingCellConditionCell FormatStop If TrueJ4Expression=MOD(ROW(),2)=0textNOJ4Expression=K4:K28="X"textNOJ4Expression=K4:K43="X"textNOJ5:J6Expression=MOD(ROW(),2)=0textNOJ5:J6Expression=K5:K44="X"textNOJ7:J43Expression=MOD(ROW(),2)=0textNOJ29:J43Expression=K29:K52="X"textNOJ7:J28Expression=K7:K46="X"textNOI4Expression=MOD(ROW(),2)=0textNOI4Expression=H4:H28="X"textNOI5:I43Expression=MOD(ROW(),2)=0textNOI29:I43Expression=H29:H52="X"textNOI5:I28Expression=H5:H29="X"textNOG4Expression=MOD(ROW(),2)=0textNOG4Expression=H4:H28="X"textNOG4Expression=H4:H43="X"textNOH4,C4:F4Expression=MOD(ROW(),2)=0textNOC4Expression=H4:H28="X"textNOD4Expression=H4:H28="X"textNOE4Expression=H4:H28="X"textNOF4Expression=H4:H28="X"textNOH4Expression=H4:H28="X"textNOC4Expression=H4:H43="X"textNOG5:G6Expression=MOD(ROW(),2)=0textNOG5:G6Expression=H5:H44="X"textNOC7:H43,C5:F6,H5:H6Expression=MOD(ROW(),2)=0textNOB4:B43Expression=AA4="N"textNOB4:B43Expression=AA4="E"textNOB4:B43Expression=AA4="A"textNOB4:B43Expression=AA4="M"textNOF29:F43Expression=H29:H52="X"textNOH29:H43Expression=H29:H52="X"textNOG29:G43Expression=H29:H52="X"textNOE29:E43Expression=H29:H52="X"textNOD29:D43Expression=H29:H52="X"textNOC29:C43Expression=H29:H52="X"textNOH5:H28Expression=H5:H44="X"textNOG7:G28Expression=H7:H46="X"textNOF5:F28Expression=H5:H44="X"textNOE5:E28Expression=H5:H44="X"textNOD5:D28Expression=H5:H44="X"textNOC5:C28Expression=H5:H44="X"textNOCells with Data ValidationCellAllowCriteriaH4:H43List=Status_DayI4:I43List=INDIRECT(H4)


----------



## Saher Naji (Sunday at 11:51 PM)

johnnyL said:


> Doesn't:
> 
> 
> ```
> ...


Got it, but I recorded this macro using Sort levels, how could I make the code recognize the four colors?


----------



## Saher Naji (Sunday at 1:28 PM)

Hello, I'm tryin to sort values based on colors, then on values

The macro was working very well, but on one sheet, so I have to create a new module for each sheet, and because I have around 400 sheets,

This is the simple working macro:

```
Sub A_Sort()
'
' A_Sort Macro
'

'
    Range("B4:J43").Select
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
        208, 142)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
        176, 132)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
        137, 219)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
        194, 230)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_3").Sort
        .SetRange Range("B3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4:B43").Select
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add2 Key:=Range("B4:B43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_3").Sort
        .SetRange Range("B4:B43")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C4").Select
End Sub
```

The best way is to run the macro for the active sheet, not for a named sheet

This my try, but it's not working, I don't know how to re-write the code to work on the active sheet


```
Sub A_Sort()
'
' A_Sort Macro
'

'
    Dim WS As Worksheet

    Set WS = ActiveSheet

    With WS.Sort
    Range("B4:J43").Select
    WS.Range("G3:G43").Sort.SortFields.Clear
    WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
        208, 142)
    WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
        176, 132)
   WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
        137, 219)
    WS.Range("G3:G43")).Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
        194, 230)
   WS.Range("G3:G43").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS.Range("G3:G43").Sort
        .SetRange Range("B3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4:B43").Select
    WS.Range("G3:G43").Sort.SortFields.Clear
   WS.Range("G3:G43").Sort.SortFields.Add2 Key:=Range("B4:B43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS.Range("G3:G43").Sort
        .SetRange Range("B4:B43")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C4").Select
End Sub
```


Thank you very much


----------



## Saher Naji (Sunday at 11:58 PM)

breynolds0431 said:


> Would you be able to provide a little example, preferably using XL2BB or sharing on dropbox, of one of the sheets to format? If you want to just provide a template/shell without any sensitive data, that should be okay. Just trying to better understand what needs to be sorted on each sheet.


Ignore the previous message please

What I'm looking for is sort the data depending on the 4 colors in column B, then on the times values in Column G, I have 365 sheets like this, so I need a code to run on active sheet
I recorded the macro using Sort Levels






test it.xlsmABCDEFGHIJK12                     SPAIN GMT+1  //   BRAZIL GMT-3              M 04:00 - 10:00   |  A 10:00 - 16:00  |   E 16:00 - 22:00  |   N 22:00 - 04:003FLIGHT #TRAVELLERFROMTOAIRLINETIMESTATUSCOMMENTS40131. RebeccaBCNEZEIB0:30→DATE CHANGED87.00 €50247. RickMXPVIEOS9:15OK60348. MarkMXPVIEOS9:15OK70447. DaleVIEKIVOS11:40OK80548. SmithVIEKIVOS11:40OK90606. JohnJFKPUJB614:49OK100790. WilliamCPTLHRBA16:40→REFUNDED110803. SandyEZELHRBA18:25→REFUNDED120903. ClaudeAEPGRUG322:35OK131084. MichaleSJOMADIB23:50OK14111512161317141815191620172118221923202421252226232724282529263027312832293330343135323633373438353936403741384239434044  TOTALS        BUMPEDUSEDBOOKINGS450710Jan_4Cell FormulasRangeFormulaF45F45=COUNTIF(H4:H43,"X")H45H45=COUNTIF(H4:H43,"OK")I45I45=COUNTA(F4:F43)Cells with Conditional FormattingCellConditionCell FormatStop If TrueJ4Expression=MOD(ROW(),2)=0textNOJ4Expression=K4:K28="X"textNOJ4Expression=K4:K43="X"textNOJ5:J6Expression=MOD(ROW(),2)=0textNOJ5:J6Expression=K5:K44="X"textNOJ7:J43Expression=MOD(ROW(),2)=0textNOJ29:J43Expression=K29:K52="X"textNOJ7:J28Expression=K7:K46="X"textNOI4Expression=MOD(ROW(),2)=0textNOI4Expression=H4:H28="X"textNOI5:I43Expression=MOD(ROW(),2)=0textNOI29:I43Expression=H29:H52="X"textNOI5:I28Expression=H5:H29="X"textNOG4Expression=MOD(ROW(),2)=0textNOG4Expression=H4:H28="X"textNOG4Expression=H4:H43="X"textNOH4,C4:F4Expression=MOD(ROW(),2)=0textNOC4Expression=H4:H28="X"textNOD4Expression=H4:H28="X"textNOE4Expression=H4:H28="X"textNOF4Expression=H4:H28="X"textNOH4Expression=H4:H28="X"textNOC4Expression=H4:H43="X"textNOG5:G6Expression=MOD(ROW(),2)=0textNOG5:G6Expression=H5:H44="X"textNOC7:H43,C5:F6,H5:H6Expression=MOD(ROW(),2)=0textNOB4:B43Expression=AA4="N"textNOB4:B43Expression=AA4="E"textNOB4:B43Expression=AA4="A"textNOB4:B43Expression=AA4="M"textNOF29:F43Expression=H29:H52="X"textNOH29:H43Expression=H29:H52="X"textNOG29:G43Expression=H29:H52="X"textNOE29:E43Expression=H29:H52="X"textNOD29:D43Expression=H29:H52="X"textNOC29:C43Expression=H29:H52="X"textNOH5:H28Expression=H5:H44="X"textNOG7:G28Expression=H7:H46="X"textNOF5:F28Expression=H5:H44="X"textNOE5:E28Expression=H5:H44="X"textNOD5:D28Expression=H5:H44="X"textNOC5:C28Expression=H5:H44="X"textNOCells with Data ValidationCellAllowCriteriaH4:H43List=Status_DayI4:I43List=INDIRECT(H4)


----------



## Saher Naji (Monday at 4:07 AM)

breynolds0431 said:


> Oh, sorry. Since you are selecting cells, which usually isn't necessary, you'd also need to select the sheet as you loop through them. Try the below.
> 
> 
> ```
> ...


it's working very well with some these edits:

```
Sub A_Sort()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

 With ws
 .Select
.Range("B4:J43").Select
.Sort.SortFields.Clear
.Sort.SortFields.Add(Range("B4:B43"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
208, 142)
.Sort.SortFields.Add(Range("B4:B43"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
176, 132)
.Sort.SortFields.Add(Range("B4:B43"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
137, 219)
.Sort.SortFields.Add(Range("B4:B43"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
194, 230)
.Sort.SortFields.Add2 Key:=Range("G4:G43") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("B3:J43")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
 .Apply
End With
.Range("B4:B43").Select
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=Range("B4:B43") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("B4:B43")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
 .Apply
End With
.Range("C4").Select
End With
Next ws

End Sub
```


----------



## Saher Naji (Monday at 4:15 AM)

The time order as shown in the screenshot above
for the first 3 colors (4:00-22:00) every thing is working well, but the current code sorting the times like this for the last period (22:00 - 4:00): 02:30; 03:00, 22:00, but it should to start with 22:00 and ends with 03:59
So could we add some lines to the current code to sort the times correctly:


```
.Sort.SortFields.Add2 Key:=Range("G4:G43") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
```


----------

