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.
Reply With Quote