troubleshooting Question

Excel VBA sort 14 contiguous columns by colors

Avatar of vbaabv
vbaabvFlag for United States of America asked on
VBA
7 Comments1 Solution484 ViewsLast Modified:
I am doing the same sort repeatedly in the same worksheet, only the rows are changing. I am basically sorting the worksheet section by section (each section being a group of contiguous rows).

The repeated sort is actually 4 similar sorts. All four are sorts of columns D thru R and are sorts by cell color.
1. Sort columns D thru R by cell color (light red, 255, 199, 206) putting light red at the top. 2. Sort columns D-R by cell color (dark red, 192, 0, 0)  puts dark red at top.
3. Sort same columns and put light green (198, 239, 206) on bottom.
4. Sort again (same columns, D-R) and put dark green (79, 98, 40) on bottom.

Caveat: some of the columns, D-R, will not have the given color. I need to check to see if the given color is present in column and if it is not then change to one of the other colors or no color.

 With each sort the rows change, but the rows are always contiguous.

This is what I now have:

Sub ColorSort()
'
' ColorSort Macro
'
    Range("A2341:Y2368").Select
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add Key:=Range( _
        "D2341:D2368"), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption _
        :=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "E2341:E2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206) ' 192, 0, 0 dark red, 198, 239, 206 light green, 79, 98, 40 dark green
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "F2341:F2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "G2341:G2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "H2341:H2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206) ' light red
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "I2341:I2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "J2341:J2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(192, 0, 0) ' dark red
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "K2341:K2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(192, 0, 0)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "L2341:L2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(192, 0, 0)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "M2341:M2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(192, 0, 0)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "N2341:N2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(192, 0, 0)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "O2341:O2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(192, 0, 0)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "P2341:P2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(192, 0, 0)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "Q2341:Q2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206)
    ActiveWorkbook.Worksheets("Sheet24 (4)").Sort.SortFields.Add(Range( _
        "R2341:R2368"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 199, 206)
    With ActiveWorkbook.Worksheets("Sheet24 (4)").Sort
        .SetRange Range("A2341:Y2368")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Can a loop shorten the number of lines of code ?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros