Search a range for a specific color of rows, relocate those cells to another sheet, return previous sheet formatting to blank and sort secondary sheet

R

Royalbloodi

New Member
Joined
Oct 31, 2023
Messages
16
Office Version
  1. 365
Platform
  1. Windows
So, this is a mouthful. I have a spreadsheet that tracks the statuses of my jobs. I need to be able to click on the sort button sort that sheet by color and relocate all completed work to a secondary sheet where it will be amended after the last row and sorted, also this code needs to reformat the cells where the relocated information was previously, back to white.

Currently, it only sorts the rows by color with this:
VBA Code: 
Private Sub CommandButton1_Click()

' Sort rows by RGB color order.
' To add a color, go down to the section for adding colors and enter the RGB color code at the end in place of the (###, ###, ###)
' To change the order of colors, modify the RGB codes below to suit your needs.  Use the Sort Order Legend to set up your colors ahead of time.

    Range("C23:O51").Select
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Clear

'1 - Sort (LightRed) first.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(244, 132, 132)

'2 - Sort (Orange) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(255, 192, 0)

'3 - Sort (Yellow) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(255, 255, 0)

'4 - Sort (Green) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(146, 208, 80)

'5 - Sort (LightBlue) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(0, 176, 240)

'6 - Sort (LightPurple) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(142, 169, 219)

'7 - Sort (GrayBlue) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(193, 152, 224)

'8 - Sort (RedHold) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(255, 0, 0)

'9 - Sort (TanP&P) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(181, 182, 150)

'                     ------------------------------------------ADD COLORS BELOW------------------------------------------
'                     ***       ONCE FINISHED, DO NOT FORGET TO REMOVE THE TIC MARKS FROM BEFORE THE LINE OF CODE       ***

'10 - Sort (Burgundy) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(175, 19, 3)

'11 - Sort (Cyan) next.
    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(0, 250, 225)

'12 - Sort (Color) next.
'    ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
'        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
    RGB(###, ###, ###)

    With ActiveWorkbook.Worksheets("JobSort").Sort
        .SetRange Range("C23:O51")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C23").Select

End Sub

Mixed Data in Cells C23 to O51.
1718987658598.png


Clicking on "Sort All" organizes the rows by Color first.
1718987724550.png


Relocates the "MOVED TO WORK" Cyan rows to another sheet called Work Order after any other existing rows, sorts them by Request Number and returns the selection cursor to C14.
1718990152444.png


Resets formatting for relocated rows to white with Bold black text.
1718987829579.png


Then returns the cursor selection to C23 on JobSort.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
I hope this helps. Modified your code a little to speed up the code compilation
VBA Code: 
Private Sub CommandButton1_Click()

' Sort rows by RGB color order.
' To add a color, go down to the section for adding colors and enter the RGB color code at the end in place of the (###, ###, ###)
' To change the order of colors, modify the RGB codes below to suit your needs.  Use the Sort Order Legend to set up your colors ahead of time.
On Error GoTo WaitHere
Begin:
    Application.ScreenUpdating = False
    With ActiveWorkbook.Worksheets("JobSort")
        .Activate
        .Range("C23:O51").Sort.SortFields.Clear
    End With
    With ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue

'1 - Sort (LightRed) first.
       .Color = _
    RGB(244, 132, 132)

'2 - Sort (Orange) next.
    
        .Color = _
    RGB(255, 192, 0)

'3 - Sort (Yellow) next.
        
        .Color = _
    RGB(255, 255, 0)

'4 - Sort (Green) next.
        
        .Color = _
    RGB(146, 208, 80)

'5 - Sort (LightBlue) next.
        
        .Color = _
    RGB(0, 176, 240)

'6 - Sort (LightPurple) next.
        
        .Color = _
    RGB(142, 169, 219)

'7 - Sort (GrayBlue) next.
        
        .Color = _
    RGB(193, 152, 224)

'8 - Sort (RedHold) next.
        
        .Color = _
    RGB(255, 0, 0)

'9 - Sort (TanP&P) next.
        
        .Color = _
    RGB(181, 182, 150)

'                     ------------------------------------------ADD COLORS BELOW------------------------------------------
'                     ***       ONCE FINISHED, DO NOT FORGET TO REMOVE THE TIC MARKS FROM BEFORE THE LINE OF CODE       ***

'10 - Sort (Burgundy) next.
        
        .Color = _
    RGB(175, 19, 3)

'11 - Sort (Cyan) next.
        
        .Color = _
    RGB(0, 250, 225)

'12 - Sort (Color) next.
'
'        .Color = _
    RGB(###, ###, ###)
    End With
    With ActiveWorkbook.Worksheets("JobSort").Sort
        .SetRange Range("C23:O51")
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C23:O51").Select
'' Collect the active working range
    With Range("D14")
        If .Offset(1, 0).Value = "" Then
        Else
            .End(xlDown).EntireRow.Select
           Select Case Q = MsgBox("You have " & .End(xlDown).EntireRow.Count & _
                " row(s) of data to export from WorkOrder", vbYesNoCancel + vbQuestion, "Export Data?")
            Case vbYes
            ' Exports to "MOVED TO WORK" sheet
                Selection.Copy
            Case Else
                MsgBox "No Data exported", vbInformation + vbApplicationModal, "Export Data"
            End Select
            
        End If
    End With
    Application.ScreenUpdating = True
Exit Sub
WaitHere: Stop
    Application.ScreenUpdating = True
GoTo Begin
End Sub
 
Upvote 0
Rhodie72 said:
I hope this helps. Modified your code a little to speed up the code compilation
VBA Code: 
Private Sub CommandButton1_Click()

' Sort rows by RGB color order.
' To add a color, go down to the section for adding colors and enter the RGB color code at the end in place of the (###, ###, ###)
' To change the order of colors, modify the RGB codes below to suit your needs.  Use the Sort Order Legend to set up your colors ahead of time.
On Error GoTo WaitHere
Begin:
    Application.ScreenUpdating = False
    With ActiveWorkbook.Worksheets("JobSort")
        .Activate
        .Range("C23:O51").Sort.SortFields.Clear
    End With
    With ActiveWorkbook.Worksheets("JobSort").Sort.SortFields.Add(Range("Chg_Date"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue

'1 - Sort (LightRed) first.
       .Color = _
    RGB(244, 132, 132)

'2 - Sort (Orange) next.
   
        .Color = _
    RGB(255, 192, 0)

'3 - Sort (Yellow) next.
       
        .Color = _
    RGB(255, 255, 0)

'4 - Sort (Green) next.
       
        .Color = _
    RGB(146, 208, 80)

'5 - Sort (LightBlue) next.
       
        .Color = _
    RGB(0, 176, 240)

'6 - Sort (LightPurple) next.
       
        .Color = _
    RGB(142, 169, 219)

'7 - Sort (GrayBlue) next.
       
        .Color = _
    RGB(193, 152, 224)

'8 - Sort (RedHold) next.
       
        .Color = _
    RGB(255, 0, 0)

'9 - Sort (TanP&P) next.
       
        .Color = _
    RGB(181, 182, 150)

'                     ------------------------------------------ADD COLORS BELOW------------------------------------------
'                     ***       ONCE FINISHED, DO NOT FORGET TO REMOVE THE TIC MARKS FROM BEFORE THE LINE OF CODE       ***

'10 - Sort (Burgundy) next.
       
        .Color = _
    RGB(175, 19, 3)

'11 - Sort (Cyan) next.
       
        .Color = _
    RGB(0, 250, 225)

'12 - Sort (Color) next.
'
'        .Color = _
    RGB(###, ###, ###)
    End With
    With ActiveWorkbook.Worksheets("JobSort").Sort
        .SetRange Range("C23:O51")
        .Header = xlGuess
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C23:O51").Select
'' Collect the active working range
    With Range("D14")
        If .Offset(1, 0).Value = "" Then
        Else
            .End(xlDown).EntireRow.Select
           Select Case Q = MsgBox("You have " & .End(xlDown).EntireRow.Count & _
                " row(s) of data to export from WorkOrder", vbYesNoCancel + vbQuestion, "Export Data?")
            Case vbYes
            ' Exports to "MOVED TO WORK" sheet
                Selection.Copy
            Case Else
                MsgBox "No Data exported", vbInformation + vbApplicationModal, "Export Data"
            End Select
           
        End If
    End With
    Application.ScreenUpdating = True
Exit Sub
WaitHere: Stop
    Application.ScreenUpdating = True
GoTo Begin
End Sub
Click to expand...
I replace my code with the previous and it just goes straight to the "WaitHere: Stop". So I commented out that and the "On Error GoTo WaitHere".
The error I get is:

Run-time error '1004':
Unable to get the Sort property of the Range class

Then it stops at:

1719598697779.png
 
Upvote 0
I think you can remove the
VBA Code: 
.range("C23:O51")
section from that line and maybe it will run correctly
 
Upvote 0
So, I'm noticing now that this seems to be trying to bring things over FROM WorkOrder. What I'm attempting to do is that whenever a job is moved to work order, it gets removed from the page with everything else and to the WorkOrder tab and sorted with the other jobs that have been previously moved to work orders.

So essentially, I'm tracking the status of my jobs on JobSort. As the job design/planning is completed, it goes to work order for construction. At this point the work is tracked, but doesn't need to be on my main tracking page. So once it's changed to the Cyan "Moved to Work" status, it gets removed from JobSort and added to the end of the WorkOrder data list. After that, it should be automatically sorted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Sort Excel worksheet with multiple colors
Replies
1
Views
339
kevin9999
K
Dazzawm
  • Question
Change Recorded Range To Used Range.
2
Replies
12
Views
588
Dazzawm
Dazzawm
E
  • Solved
Run the same Macro on All WorkSheets
Replies
3
Views
328
CephasOz
CephasOz
S
  • Question
Worksheets Buttons Update
Replies
0
Views
477
sharpeye
S
F
  • Question
Code is missing Something
Replies
2
Views
265
farr80
F

Forum statistics

Threads
1,219,016
Messages
6,145,771
Members
450,643
Latest member
itresource

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top