Finding Certain Values in Row 6 and filtering them

[drozek]

So I have this macro written for my sales team. My boss asked me since G6 "Ship - To" can be in A6, B6, C6, D6, E6 at anytime. Can I locate it and then do my filters. and sort.

Any help with that?

Sub ColorEnds()
'
' ColorEnds Macro
' Ctrl+Shift+E

'
    Range("H7").Select
    ActiveWindow.FreezePanes = True
    If Range("G6") <> "Ship - To" Then
    MsgBox "Only One Ship To Selected in SAP! or No Ship To's  in SAP", vbOKOnly, "WARNING!"
    Exit Sub
    End If
    Cells.Select
    Cells.EntireColumn.AutoFit
    Rows("6:6").Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
    Call ShipTo
  With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
    .AutoFilter 1, "Sales Input"
    .AutoFilter 7, "<>Total"
    Columns("A:A").ColumnWidth = 0
    Rows("1:5").Select
    Range("A5").Activate
    Selection.EntireRow.Hidden = True
  End With
  'Call ShipTo
End Sub

Sub ShipTo()
Range("G6").CurrentRegion.Select
    Selection.Sort Key1:=Range("G6"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

End Sub

 

[rlv01]

One way.

Sub ColorEnds()
'
' ColorEnds Macro
' Ctrl+Shift+E

'
    Range("H7").Select
    ActiveWindow.FreezePanes = True

    If Not Range("A6:E6").Find(What:="Ship - To", LookAt:=xlPart) Is Nothing Then
        Range("G6").Value = "Ship - To"
    End If

    If Range("G6") <> "Ship - To" Then
        MsgBox "Only One Ship To Selected in SAP! or No Ship To's  in SAP", vbOKOnly, "WARNING!"
        Exit Sub
    End If
    Cells.Select
    Cells.EntireColumn.AutoFit
    Rows("6:6").Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
    Call ShipTo
    With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
        .AutoFilter 1, "Sales Input"
        .AutoFilter 7, "<>Total"
        Columns("A:A").ColumnWidth = 0
        Rows("1:5").Select
        Range("A5").Activate
        Selection.EntireRow.Hidden = True
    End With
    'Call ShipTo
End Sub