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

Saher Naji · Jan 8, 2023

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:

VBA Code:

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

VBA Code:

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 · Jan 8, 2023

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.xlsm

A

B

C

D

E

F

G

H

I

J

K

1

2

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:00

3

FLIGHT #

TRAVELLER

FROM

TO

AIRLINE

TIME

STATUS

COMMENTS

4

01

31. Rebecca

BCN

EZE

IB

0:30

→

DATE CHANGED

87.00 €

5

02

47. Rick

MXP

VIE

OS

9:15

OK

6

03

48. Mark

MXP

VIE

OS

9:15

OK

7

04

47. Dale

VIE

KIV

OS

11:40

OK

8

05

48. Smith

VIE

KIV

OS

11:40

OK

9

06

06. John

JFK

PUJ

B6

14:49

OK

10

07

90. William

CPT

LHR

BA

16:40

→

REFUNDED

11

08

03. Sandy

EZE

LHR

BA

18:25

→

REFUNDED

12

09

03. Claude

AEP

GRU

G3

22:35

OK

13

10

84. Michale

SJO

MAD

IB

23:50

OK

14

11

15

12

16

13

17

14

18

15

19

16

20

17

21

18

22

19

23

20

24

21

25

22

26

23

27

24

28

25

29

26

30

27

31

28

32

29

33

30

34

31

35

32

36

33

37

34

38

35

39

36

40

37

41

38

42

39

43

40

44

TOTALS

BUMPED

USED

BOOKINGS

45

0

7

10

Jan_4

Cell Formulas
Range	Formula
F45	F45	=COUNTIF(H4:H43,"X")
H45	H45	=COUNTIF(H4:H43,"OK")
I45	I45	=COUNTA(F4:F43)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J4	Expression	=MOD(ROW(),2)=0	text	NO
J4	Expression	=K4:K28="X"	text	NO
J4	Expression	=K4:K43="X"	text	NO
J5:J6	Expression	=MOD(ROW(),2)=0	text	NO
J5:J6	Expression	=K5:K44="X"	text	NO
J7:J43	Expression	=MOD(ROW(),2)=0	text	NO
J29:J43	Expression	=K29:K52="X"	text	NO
J7:J28	Expression	=K7:K46="X"	text	NO
I4	Expression	=MOD(ROW(),2)=0	text	NO
I4	Expression	=H4:H28="X"	text	NO
I5:I43	Expression	=MOD(ROW(),2)=0	text	NO
I29:I43	Expression	=H29:H52="X"	text	NO
I5:I28	Expression	=H5:H29="X"	text	NO
G4	Expression	=MOD(ROW(),2)=0	text	NO
G4	Expression	=H4:H28="X"	text	NO
G4	Expression	=H4:H43="X"	text	NO
H4,C4:F4	Expression	=MOD(ROW(),2)=0	text	NO
C4	Expression	=H4:H28="X"	text	NO
D4	Expression	=H4:H28="X"	text	NO
E4	Expression	=H4:H28="X"	text	NO
F4	Expression	=H4:H28="X"	text	NO
H4	Expression	=H4:H28="X"	text	NO
C4	Expression	=H4:H43="X"	text	NO
G5:G6	Expression	=MOD(ROW(),2)=0	text	NO
G5:G6	Expression	=H5:H44="X"	text	NO
C7:H43,C5:F6,H5:H6	Expression	=MOD(ROW(),2)=0	text	NO
B4:B43	Expression	=AA4="N"	text	NO
B4:B43	Expression	=AA4="E"	text	NO
B4:B43	Expression	=AA4="A"	text	NO
B4:B43	Expression	=AA4="M"	text	NO
F29:F43	Expression	=H29:H52="X"	text	NO
H29:H43	Expression	=H29:H52="X"	text	NO
G29:G43	Expression	=H29:H52="X"	text	NO
E29:E43	Expression	=H29:H52="X"	text	NO
D29:D43	Expression	=H29:H52="X"	text	NO
C29:C43	Expression	=H29:H52="X"	text	NO
H5:H28	Expression	=H5:H44="X"	text	NO
G7:G28	Expression	=H7:H46="X"	text	NO
F5:F28	Expression	=H5:H44="X"	text	NO
E5:E28	Expression	=H5:H44="X"	text	NO
D5:D28	Expression	=H5:H44="X"	text	NO
C5:C28	Expression	=H5:H44="X"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H4:H43	List	=Status_Day
I4:I43	List	=INDIRECT(H4)

Saher Naji · Jan 9, 2023

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.

VBA Code:

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

it's working very well with some these edits:

VBA Code:

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 · Jan 9, 2023

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:

VBA Code:

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

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

Saher Naji

Board Regular

Saher Naji

Board Regular

Excel Facts

Saher Naji

Board Regular

Saher Naji

Board Regular

Similar threads

Forum statistics

Share this page

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

Saher Naji

Board Regular

Saher Naji

Board Regular

Excel Facts

Saher Naji

Board Regular

Saher Naji

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock