Sub ColorEnds()
'
' ColorEnds Macro
' Ctrl+Shift+E
'
Dim ShipColumn As Integer
Dim FirstColumn As Integer
Dim Material As Integer
Dim f As Range
Set f = Cells.Find("Ship - To", , xlValues, x1Part, , xlNext, False, , False)
If f Is Nothing Then
Call OneShipTo
Else
f.Activate
End If
End Sub
Sub FindMonth()
Cells.Find(What:="M 0*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
ActiveWindow.FreezePanes = True
End Sub
Sub MultipleShipTo()
Cells.Find(What:="Ship - To", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ShipColumn = ActiveCell.Column
Cells.Find(What:="DP FORECAST", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
FirstColumn = ActiveCell.Column
Rows("6:6").Select
Selection.AutoFilter
With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
.AutoFilter FirstColumn, "Sales Input"
.AutoFilter ShipColumn, "<>Total"
End With
Columns("A:A").ColumnWidth = 0
Rows("1:5").Select
Range("A5").Activate
Selection.EntireRow.Hidden = True
Cells.Select
Cells.EntireColumn.AutoFit
Call FindMonth
End Sub
Sub OneShipTo()
Cells.Find(What:="Material", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Material = ActiveCell.Column
Cells.Find(What:="DP FORECAST", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
FirstColumn = ActiveCell.Column
Rows("6:6").Select
Selection.AutoFilter
With ActiveSheet.Range("A5:G" & Range("A" & Rows.Count).End(3).Row)
.AutoFilter FirstColumn, "Sales Input"
.AutoFilter Material, "<>Total"
End With
Columns("A:A").ColumnWidth = 0
Rows("1:5").Select
Range("A5").Activate
Selection.EntireRow.Hidden = True
Cells.Select
Cells.EntireColumn.AutoFit
Call FindMonth
End Sub