Sub Painting_Dept_Identifier()
Dim start_Time#, End_Time#
Main_Order_No_Col = Range("Main_Order_Col").Column
Main_Dept_Col = Range("main_line_Number").Column
Main_Line_Column = Range("Main_Line_Number_02").Column
Main_Header_Row = Range("planning_header_row").Row + 2
'>>>Reading data from loading Zones
Row_Start = Range("Loading_Zones").Row
Column_Start = Range("Loading_Zones").Column
Column_End = Column_Start + Range("Loading_Zones").Columns.Count - 1
loading_Zones_row = Range("Loading_Zones").Row
'>>> doing the loop to place the color 1st on the loading data sheet
KL_Row_Index = Main_Header_Row
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
start_Time = Timer
'Range("Loading_Zones").ClearFormats
Application.StatusBar = "Planning Calculation : Painting In Progress"
Do While Range("Main!A1").Offset(KL_Row_Index - 1, Main_Order_No_Col - 1) <> ""
Group_Identifier = Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1)
Select Case Group_Identifier
'>>> doing the loop to place the color 1st on the loading data sheet
Case Is = 1
Range("TATAMO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 2
Range("JBOURG").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 3
Range("ROME").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 4
Range("BERLIN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 5
Range("TOLIPA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 6
Range("VENISE").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 7
Range("_3_MIOVA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 8
Range("M_DERA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 9
Range("BRUXELLES").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 10
Range("N.YORK").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 11
Range("PEKIN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 12
Range("M_CAR").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 13
Range("GENEVE").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 14
Range("TOKY").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 15
Range("EDEN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 16
Range("PARIS").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 17
Range("TOKYO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 18
Range("P.LOUIS").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 19
Range("MEXICO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 20
Range("SYDNEY").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 21
Range("MUMBAI").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 22
Range("MADRID").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 23
Range("LONDON").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 24
Range("MAPUTO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 25
Range("DUBLIN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 26
Range("RIO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 27
Range("LISBONE").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 28
Range("PREPA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
Case Is = 29
Range("SUBCON").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1).PasteSpecial xlPasteFormats
Range("Main!A1").Offset(KL_Row_Index - 1, Main_Line_Column - 1).PasteSpecial xlPasteFormats
End Select
For Column_Index = Column_Start To Column_End
'>>>> LOOP WHEN ZERO VALUES FOR EFFICIENT COLORING
If Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1) = 0 Then
GoTo Skip_Column
End If
'>>>>>>>>>>>>>>> Identify the group to color and start setting the colors on the loading zones of each _
order and row.
Group_Identifier = Range("Main!A1").Offset(KL_Row_Index - 1, Main_Dept_Col - 1)
Select Case Group_Identifier
'>>> doing the loop to place the color 1st on the loading data sheet
Case Is = 1
Range("TATAMO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 2
Range("JBOURG").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 3
Range("ROME").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 4
Range("BERLIN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 5
Range("TOLIPA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 6
Range("VENISE").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 7
Range("_3_MIOVA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 8
Range("M_DERA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 9
Range("BRUXELLES").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 10
Range("N.YORK").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 11
Range("PEKIN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 12
Range("M_CAR").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 13
Range("GENEVE").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 14
Range("TOKY").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 15
Range("EDEN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 16
Range("PARIS").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 17
Range("TOKYO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 18
Range("P.LOUIS").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 19
Range("MEXICO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 20
Range("SYDNEY").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 21
Range("MUMBAI").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 22
Range("MADRID").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 23
Range("LONDON").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 24
Range("MAPUTO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 25
Range("DUBLIN").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 26
Range("RIO").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 27
Range("LISBONE").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 28
Range("PREPA").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
Case Is = 29
Range("SUBCON").Copy
Range("Main!A1").Offset(KL_Row_Index - 1, Column_Index - 1).PasteSpecial xlPasteFormats
End Select
'End If
SKIP_ROW:
Skip_Column:
Next Column_Index
'loading_Zones_row = loading_Zones_row + 1
KL_Row_Index = KL_Row_Index + 1
Loop
'
' ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
' , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
' AllowFormattingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
' AllowFiltering:=True
'Sheets("Master_Visual").Range("MVisual_Str") = "Y"
End_Time = Timer
Application.StatusBar = " Painting Completed In " & Format(End_Time - start_Time, "0.000") & "secs"
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub