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

breynolds0431 · Jan 8, 2023

Hi there. Give this worksheet loop a try.

VBA Code:

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

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

Saher Naji · Jan 8, 2023

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

VBA Code:

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

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.

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

Thanks for your cooperation
I faced another issue

breynolds0431 · Jan 8, 2023

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

Doesn't:

VBA Code:

    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:

VBA Code:

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

?

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.

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

johnnyL said:

Doesn't:

VBA Code:

    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:

VBA Code:

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

?

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

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

Saher Naji

Board Regular

Excel Facts

breynolds0431

Active Member

Saher Naji

Board Regular

Saher Naji

Board Regular

breynolds0431

Active Member

Saher Naji

Board Regular

breynolds0431

Active Member

johnnyL

Well-known Member

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

Board Regular

Excel Facts

Active Member

Board Regular

Board Regular

Active Member

Board Regular

Active Member

Well-known Member

Board Regular

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