VISHAL120
08-05-2020, 04:05 AM
Hello Everyone,
I hope everyone are safe and is taking good precaution during this pandemic period. Here in my country is still very high.
I am actually using the below code to “paint the rows and column” to better identify each Production line that has been planned.
Actually only the painting process is taking almost 60 secs to complete. Because the more we add the more time its taking. Right now if it is run it takes like 36 secs - 40 secs as I have decrease some of the orders in it.
Note : this is only a part of the planning system that I have attached as I am still working on it to make it more quicker.
What I have done to be able to do the painting of the rows and columns till now:
1. I have defined all the colors on a sheet which is name sheet “ lines”. I am attaching the file for better understanding. You can click on the button Click to Paint the production lines to see what it does for the painting.
2. The each production lines color are define on the column C from the sheet "lines" according to the Production line name. Just have a lot if not clear.
3. Then after we have done the planning we shall click on the button “Click to Paint the production lines” which runs the code for the painting.
The code shall paint the columns:
column G ,
column H and
Column CK to Column IC for which starting from cell CK12 to CI214 I have named it as Loading_Zone1 to be able to address the column and rows accordingly.
here is code :
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
I will be very grateful if I can get some guidance for speeding the painting process to may be 10 secs or less as actually its taking me almost 60 secs to complete the whole painting to identify the production lines.
Thank you in advance for all advise and recommendations.
Have a nice day and stay safe.
I hope everyone are safe and is taking good precaution during this pandemic period. Here in my country is still very high.
I am actually using the below code to “paint the rows and column” to better identify each Production line that has been planned.
Actually only the painting process is taking almost 60 secs to complete. Because the more we add the more time its taking. Right now if it is run it takes like 36 secs - 40 secs as I have decrease some of the orders in it.
Note : this is only a part of the planning system that I have attached as I am still working on it to make it more quicker.
What I have done to be able to do the painting of the rows and columns till now:
1. I have defined all the colors on a sheet which is name sheet “ lines”. I am attaching the file for better understanding. You can click on the button Click to Paint the production lines to see what it does for the painting.
2. The each production lines color are define on the column C from the sheet "lines" according to the Production line name. Just have a lot if not clear.
3. Then after we have done the planning we shall click on the button “Click to Paint the production lines” which runs the code for the painting.
The code shall paint the columns:
column G ,
column H and
Column CK to Column IC for which starting from cell CK12 to CI214 I have named it as Loading_Zone1 to be able to address the column and rows accordingly.
here is code :
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
I will be very grateful if I can get some guidance for speeding the painting process to may be 10 secs or less as actually its taking me almost 60 secs to complete the whole painting to identify the production lines.
Thank you in advance for all advise and recommendations.
Have a nice day and stay safe.