Ситуация : ирландское отделение "мелкософта" что-то нагадило в мозг нашему начальнику, в итоге у мну появилась указЮлька сотрудничать с этими ирландскими мелкософтовцами по вопросу проверки лицензионности используемого нами ПО от мелкософта.
Мелкософт прислал Excel-кий файл, который я должен был заполнить по кол-ву и качеству ПО. С тем Excel-ким файлов никаких проблем не было (в части макросов). После ихнего анализа написанных мною данных, они прислали второй файл и вот со вторым файлом у мну возникли подозрения.
Проблема : во втором файле оказался макрос, самое интересное, что ни 2007 - 2013 офис ничего не сказал про наличие макроса, хотя в настройках Excel-я этих версий стоит: " Отключить все макросы с уведомлением ". А на запуск макроса среагировал только старенький 2003 Excel, сказав, что "файл содержит макрос: запускаем?".
Вопрос: посмотрите, что делает макрос в этом файле?
Код, что выдал VB в модуле привожу в спойлере. Кстати макрос имеет сертификат и права принадлежат "Accordo Group Limited"
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
318.
319.
320.
321.
322.
323.
324.
325.
326.
327.
328.
329.
330.
331.
332.
333.
334.
335.
336.
337.
338.
339.
340.
341.
342.
343.
344.
345.
346.
347.
348.
349.
350.
351.
352.
353.
354.
355.
356.
357.
358.
359.
360.
361.
362.
363.
364.
365.
366.
367.
368.
369.
370.
371.
372.
373.
374.
375.
376.
377.
378.
379.
380.
381.
382.
383.
384.
385.
386.
387.
388.
389.
390.
391.
392.
393.
394.
395.
396.
397.
398.
399.
400.
401.
402.
403.
404.
405.
406.
407.
408.
409.
410.
411.
412.
413.
414.
415.
416.
417.
418.
419.
420.
421.
422.
423.
424.
425.
426.
427.
428.
429.
430.
431.
432.
433.
434.
435.
436.
437.
438.
439.
440.
441.
442.
443.
444.
445.
446.
447.
448.
449.
450.
451.
452.
453.
454.
455.
456.
457.
458.
459.
460.
461.
462.
463.
464.
465.
466.
467.
468.
469.
470.
471.
472.
473.
474.
475.
476.
477.
478.
479.
480.
481.
482.
483.
484.
485.
486.
487.
488.
489.
490.
491.
492.
493.
494.
495.
496.
497.
498.
499.
500.
501.
502.
503.
504.
505.
506.
507.
508.
509.
510.
511.
512.
513.
514.
515.
516.
517.
518.
519.
520.
521.
522.
523.
524.
525.
526.
527.
528.
529.
530.
531.
532.
533.
534.
535.
536.
537.
538.
539.
540.
541.
542.
543.
544.
545.
546.
547.
548.
549.
550.
551.
552.
553.
554.
555.
556.
557.
558.
559.
560.
561.
562.
563.
564.
565.
566.
567.
568.
569.
570.
571.
572.
573.
574.
575.
576.
577.
578.
579.
580.
581.
582.
583.
584.
585.
586.
587.
588.
589.
590.
591.
592.
593.
594.
595.
596.
597.
598.
599.
600.
601.
602.
603.
604.
605.
606.
607.
608.
609.
610.
611.
612.
613.
614.
615.
616.
617.
618.
619.
620.
621.
622.
623.
624.
625.
626.
627.
628.
629.
630.
631.
632.
633.
634.
635.
636.
637.
638.
639.
640.
641.
642.
643.
644.
645.
646.
647.
648.
649.
650.
651.
652.
653.
654.
655.
656.
657.
658.
659.
660.
661.
662.
663.
664.
665.
666.
667.
668.
669.
670.
671.
672.
673.
674.
675.
676.
677.
678.
679.
680.
681.
682.
683.
684.
685.
686.
687.
688.
689.
690.
691.
692.
693.
694.
695.
696.
697.
698.
699.
700.
701.
702.
703.
704.
705.
706.
707.
708.
709.
710.
711.
712.
713.
714.
715.
716.
717.
718.
719.
720.
721.
722.
723.
724.
725.
726.
727.
728.
729.
730.
731.
732.
733.
734.
735.
736.
737.
738.
739.
740.
741.
742.
743.
744.
745.
746.
747.
748.
749.
750.
' 20130408 - Defect #2103 - JK - Addition of flag 266,273,999 to not highlight
' 20130501 - Task #29906 - JK - took out 54,86 and added 278,282 and 24,40,61,62,93,114,118,127,130,131,142,147,153,155,156,162,184,186,191-193,198,201-203,205,208,210,213-217,230,232,234,237,238,244-248,255,10035,10061-62
' 20130806 - Task #29988 - JK - Addition of flag 289 to not highlight
' 20130822 - Task #29538 - JH - New TurnRed prompt to warn users of incomplete (XXX, ZZ) palced consultantflags
' 20130910 - Task #29538 - JH - Additional incomplete placeholders added
' 20130920 - Defect# 2513 - JH - Removal of unwanted rows for total (Grey) rows
' 20131001 - Defect# 2513 - JH - Alteration to logic for removal of unwanted rows for total (Grey) rows
' 20131008 - Feature #130 - DRB - Consultant Flag addition - Flag 295 (added to no highlight list)
' 20131008 - Feature #142 - DRB - Consultant Flag addition - Flag 296 (added to no highlight list)
' 20131210 - Feature #320 - SCW - Consultant Flag addition - Flag 299 (added to no highlight list)
' 20131210 - Feature #337 - SCW - Consultant Flag addition - Flag 301,265 (added to no highlight list)
' 20140305 - Defect #2650 - JK - Reapplying scotts general comments changes
' 20131119 - Feature #216 - JK - Addition of flag 86 to no highlight list Reapply
' 20131119 - Feature #215 - JK - Addition of flag 297 to no highlight list Reapply
' 20131119 - Feature #151 - JK - Addition of flag 229 to no highlight list Reapply
' 20131206 - Defect #2670 - JH - Removal of '*MM*' from conditions to TurnRed
Option Explicit
Const HEADING_ROW As Integer = 4 ' row number where special column headings occur
Const UNLOCK_ROW As String = "Unlock General Consultant Queries" ' what row to look for unlocking
Const DELETE_ROW As String = "Delete Consultant Flags Below" ' what row to look for deleting consultant flags
Const GENERALS_ROW_END As String = "General Consultant Queries End"
Const COLUMN_NUMBERS As Integer = 25 ' number of possbile columns
Const ENGLISH_TEXT As String = "English"
Const CONSULT_TEXT As String = "Consultant Comments" ' what column to look for consultant comments
Const CUSTOMER_TEXT As String = "Customer Response" ' what column to look for to make yellow
Dim ENGLISH_COL As Integer ' where the English text Column is
Dim CONSULT_COL As Integer ' where the Consultant text Column is
Dim CUSTOMER_COL As Integer ' where the Customer text Column is
Dim DELETE_UNLOCK As Integer ' what row is the unlock text on
Dim DELETE_GENERAL_QUERIES_END As Integer ' what row is the unlock text ends
Dim FLAGCOLUMN As Integer ' what column holds the FLAG ID
Public Sub CleanUpLOP()
' The main procdure that cleans the LOP workbook
' Set this here to accomodate the US and UK sites
DELETE_GENERAL_QUERIES_END = 0
On Error GoTo ErrorHandler
If ValidateVariance Then
Application.ScreenUpdating = False
' Set global variables
SetVariables
'Defect 2513 Check that there are any unwanted row totals
If (RemoveUnwantedRows) Then
SetVariables 'If there is then reset the global variables
End If
Dim stopExecution As Boolean
stopExecution = TurnRed()
If (stopExecution) Then
Dim iRet As Integer
iRet = MsgBox("Please check your placed Consultant Flags, incomplete flags are coloured red. If you wish to continue this Clean Up run please press 'OK', Or 'Cancel' to stop and make corrections.", vbOKCancel, "Incomplete Consultant Flags")
If (iRet = vbCancel) Then
Exit Sub
End If
End If
' Hide English Column
HideEnglishLanguage
' Delete UnUsed Consultant Flags
RemoveConsultantFlagsDownwards
' Turn Comments interior Yellow
TurnYellow
' Delete the BUTTON
Dim Button As Shape
On Error Resume Next
Set Button = ActiveSheet.Shapes("Button 1")
On Error GoTo 0
If Not Button Is Nothing Then
ActiveSheet.Shapes("Button 1").Delete
End If
' Protect sheet and remove rows
ProtectSheet
Else
Call MsgBox("There is an error with the variance totals." _
& vbCrLf & "" _
& vbCrLf & "Please check these and try again." _
, vbExclamation Or vbSystemModal, "LOP Clean up")
End If
Exit Sub
ErrorHandler:
Call MsgBox("An error has occurred:" _
& vbCrLf & "" _
& vbCrLf & Err.Description _
& vbCrLf & "" _
& vbCrLf & "Please check this with an administrator and try again." _
, vbExclamation Or vbSystemModal, "Error Occured")
End Sub
Private Function ValidateVariance() As Boolean
' Validates the Variance numbers in the LOP
' at the moment it is not determined how to successfully validate the variances
' there is no way in which to know what column holds the variances unless hardcoded
ValidateVariance = True
End Function
Private Sub ProtectSheet()
' Protect the sheet, allow certain entry cells and remove special rows
Dim ROW_UNLOCK As Integer
If (ENGLISH_COL > 0) Then
ROW_UNLOCK = ENGLISH_COL
Else
ROW_UNLOCK = CUSTOMER_COL + 1
End If
' lock all cells on sheet
Cells.Locked = True
' open certain cells
'Range(Cells(HEADING_ROW, CONSULT_COL), Cells(LastRow("A", , ActiveSheet.Name), CONSULT_COL)).Locked = False
Range(Cells(HEADING_ROW, CUSTOMER_COL), Cells(LastRow("A", , ActiveSheet.Name), CUSTOMER_COL)).Locked = False
'We now have merged cells in here so cannot just unlock the whole column. But its hidden so not even sure we need this?
Dim c As Range
For Each c In Range(Cells(HEADING_ROW, ROW_UNLOCK), Cells(LastRow("A", , ActiveSheet.Name), ROW_UNLOCK))
c.MergeArea.Locked = False
Next
'Range(Cells(HEADING_ROW, ENGLISH_COL), Cells(LastRow("A", , ActiveSheet.Name), ENGLISH_COL)).EntireColumn.Locked = False
UnlockQuery
' remove special rows
DeleteSpecialRows
' protect worksheet
ActiveSheet.Protect Password:="asdfg", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowInsertingColumns:=True
End Sub
Private Sub UnlockQuery()
' Unlock the row for general query notes
Dim theLastRow As Long
Dim i As Integer
Dim j As Integer ' blank row counter
Dim k As Integer
Dim GeneralSection As Boolean
GeneralSection = False
theLastRow = LastRow("A", , ActiveSheet.Name)
For i = theLastRow To HEADING_ROW Step -1
If ActiveSheet.Cells(i, 1).Value = UNLOCK_ROW Then
GeneralSection = False
' unlocks 2rd row below this
ActiveSheet.Cells(i + 2, 1).EntireRow.Locked = False
'Re-lock the General Consultants Query input cell
ActiveSheet.Cells(i + 2, 1).Locked = True
' ActiveSheet.Cells(i + 2, 1).EntireRow.WrapText = True
DELETE_UNLOCK = i ' keep track of the row number for later use
Exit For
End If
'This is the header row, it needs to remain locked
'If GeneralSection = True Then
' ActiveSheet.Cells(i, 1).EntireRow.Locked = False
'End If
If ActiveSheet.Cells(i, 1).Value = GENERALS_ROW_END Then
GeneralSection = True
End If
Next i
End Sub
Private Sub DeleteSpecialRows()
' delete the query unlock row and heading row
If (Not DELETE_GENERAL_QUERIES_END = 0) Then
Cells(DELETE_GENERAL_QUERIES_END, 1).EntireRow.Hidden = True
'Cells(DELETE_GENERAL_QUERIES_END, 1).Value = ""
End If
If DELETE_UNLOCK > 0 Then
Cells(DELETE_UNLOCK, 1).EntireRow.Hidden = True
' Cells(DELETE_UNLOCK, 1).Value = ""
End If
If HEADING_ROW > 0 Then
Cells(HEADING_ROW, 1).EntireRow.Hidden = True
Cells(HEADING_ROW, 1).Value = ""
End If
End Sub
'Defect 2513 Function to remove any unwanted merged rows with no corresponding consultant flag (Grey rows) that have been manually removed
Private Function RemoveUnwantedRows() As Boolean
Dim i As Integer
Dim theLastRow As Long
RemoveUnwantedRows = False
If CONSULT_COL > 0 And CUSTOMER_COL > 0 Then
theLastRow = LastRow("A", , ActiveSheet.Name)
If DELETE_UNLOCK > 0 Then
For i = HEADING_ROW + 1 To theLastRow
If (Trim(ActiveSheet.Cells(i, 1).Value) = "Unlock General Consultant Queries") Then
DELETE_GENERAL_QUERIES_END = i
Exit For
End If
'Delete the unwanted row
If ((Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) = "") And (ActiveSheet.Cells(i, (1)).MergeArea.Interior.Color = RGB(192, 192, 192)) And (ActiveSheet.Cells(i, (CONSULT_COL - 1)).MergeCells) And (ActiveSheet.Cells(i, (CONSULT_COL - 2)).MergeCells)) Then
If ((Trim(ActiveSheet.Cells(i, (1)).Value) = "")) Then
ActiveSheet.Cells(i, 1).EntireRow.Delete
RemoveUnwantedRows = True 'Set the return boolean
Else
If ((Trim(ActiveSheet.Cells((i + 1), (CONSULT_COL)).Value) <> "") And (ActiveSheet.Cells((i + 1), (1)).MergeArea.Interior.Color = RGB(192, 192, 192)) And (ActiveSheet.Cells((i + 1), (CONSULT_COL - 1)).MergeCells) And (ActiveSheet.Cells((i + 1), (CONSULT_COL - 2)).MergeCells)) Then
'Copy over values
Dim summaryCol As String
Dim adjustVarCol As String
Dim downgradesCol As String
summaryCol = ActiveSheet.Cells(i, 1).Value
adjustVarCol = ActiveSheet.Cells(i, (CONSULT_COL - 1)).Value
downgradesCol = ActiveSheet.Cells(i, (CONSULT_COL - 2)).Value
'Delete unwanted row
ActiveSheet.Cells(i, 1).EntireRow.Delete
RemoveUnwantedRows = True 'Set the return boolean
i = i - 1
'Copy values back into new row
ActiveSheet.Cells((i + 1), (1)).Value = summaryCol
ActiveSheet.Cells((i + 1), (CONSULT_COL - 1)).Value = adjustVarCol
ActiveSheet.Cells((i + 1), (CONSULT_COL - 2)).Value = downgradesCol
End If
End If
End If
Next i
End If
End If
End Function
Private Function TurnRed() As Boolean
Dim i As Integer
Dim j As Integer
Dim theLastRow As Long
Dim EnglishConsultantFlags As Long
EnglishConsultantFlags = 0
If CONSULT_COL > 0 And CUSTOMER_COL > 0 Then
theLastRow = LastRow("A", , ActiveSheet.Name)
If DELETE_UNLOCK > 0 Then
For j = DELETE_UNLOCK + 2 To theLastRow
If (Trim(ActiveSheet.Cells(j, 1).Value) = "General Consultant Queries End") Then
DELETE_GENERAL_QUERIES_END = j
Exit For
End If
'turn text red if required
If (Trim(ActiveSheet.Cells(j, (1)).Value) Like "*XX*") Or (Trim(ActiveSheet.Cells(j, (1)).Value) Like "*ZZ*") Or (Trim(ActiveSheet.Cells(j, (1)).Value) Like "*QQ*") Or (Trim(ActiveSheet.Cells(j, (1)).Value) Like "*JJ*") Or (Trim(ActiveSheet.Cells(j, (1)).Value) Like "*AAA*") Or (Trim(ActiveSheet.Cells(j, (1)).Value) Like "*DD*") Or (Trim(ActiveSheet.Cells(j, (1)).Value) Like "*YY*") Or (UCase(Trim(ActiveSheet.Cells(j, (1)).Value)) Like "*[[]*[]]*") Then
ActiveSheet.Cells(j, 1).Font.Color = RGB(255, 0, 0)
TurnRed = True
Else
ActiveSheet.Cells(j, 1).Font.Color = RGB(0, 0, 0)
End If
Next j
End If
For i = HEADING_ROW + 1 To DELETE_GENERAL_QUERIES_END
'turn text red if required
If (Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) Like "*XX*") Or (Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) Like "*ZZ*") Or (Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) Like "*QQ*") Or (Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) Like "*JJ*") Or (Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) Like "*AAA*") Or (Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) Like "*DD*") Or (Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value) Like "*YY*") Or (UCase(Trim(ActiveSheet.Cells(i, (CONSULT_COL)).Value)) Like "*[[]*[]]*") Then
ActiveSheet.Cells(i, CONSULT_COL).Font.Color = RGB(255, 0, 0)
TurnRed = True
Else
ActiveSheet.Cells(i, CONSULT_COL).Font.Color = RGB(0, 0, 0)
End If
Next i
End If
End Function
Private Sub TurnYellow()
' Turn the range's interior colour Yellow
Dim i As Integer
Dim j As Integer
Dim theLastRow As Long
Dim EnglishConsultantFlags As Long
EnglishConsultantFlags = 0
If CONSULT_COL > 0 And CUSTOMER_COL > 0 Then
theLastRow = LastRow("A", , ActiveSheet.Name)
If DELETE_UNLOCK > 0 Then
For j = DELETE_UNLOCK + 2 To theLastRow
If (Trim(ActiveSheet.Cells(j, 1).Value) = "General Consultant Queries End") Then
DELETE_GENERAL_QUERIES_END = j
Exit For
ElseIf (Trim(ActiveSheet.Cells(j, 1).Value) <> "") Or (Trim(ActiveSheet.Cells(j, 3).Value) <> "") Then
'Copy across
If ENGLISH_COL > 0 Then
ActiveSheet.Cells(j, ENGLISH_COL).Value = ActiveSheet.Cells(j, 3).Value
End If
If (ActiveSheet.Cells(j, 1).Font.Name = "MS PGothic") And Not (ActiveSheet.Cells(j, 1).Value Like ("*" + Chr(10))) Then
'Japanese font that has been pasted from PopUp window
If (Len(ActiveSheet.Cells(j, 1).Value) > 250) Then
ActiveSheet.Cells(j, 1).Value = ActiveSheet.Cells(j, 1).Value + Chr(10) + Chr(10)
Else
ActiveSheet.Cells(j, 1).Value = ActiveSheet.Cells(j, 1).Value + Chr(10)
End If
ActiveSheet.Cells(j, 1).VerticalAlignment = xlVAlignTop
End If
ActiveSheet.Cells(j, 3).Value = ""
If (Not ActiveSheet.Cells(j, 3).MergeCells) Then
Range(ActiveSheet.Cells(j, 3), ActiveSheet.Cells(j, 3 + 3)).Merge
End If
If (ActiveSheet.Cells(j, FLAGCOLUMN).MergeCells) Then
ActiveSheet.Cells(j, FLAGCOLUMN).UnMerge
End If
If ActiveSheet.Cells(j, 7).Value <> "" Then
ActiveSheet.Cells(j, FLAGCOLUMN).Value = ActiveSheet.Cells(j, 7).Value
ActiveSheet.Cells(j, 7).Value = ""
End If
If (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 15) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 16) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 17) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 21) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 24) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 35) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 36) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 40) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 49) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 50) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 57) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 61) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 62) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 76) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 78) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 81) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 82) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 83) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 84) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 86) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 91) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 93) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 96) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 99) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 114) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 118) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 127) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 128) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 129) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 130) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 131) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 136) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 142) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 147) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 150) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 152) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 153) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 154) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 155) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 156) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 162) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 184) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 185) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 186) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 187) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 191) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 192) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 193) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 194) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 195) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 197) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 198) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 200) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 201) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 202) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 203) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 204) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 205) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 208) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 210) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 212) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 213) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 214) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 215) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 216) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 217) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 220) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 229) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 230) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 231) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 232) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 234) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 235) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 237) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 238) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 244) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 245) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 246) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 247) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 248) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 255) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 266) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 273) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 275) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 278) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 282) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 289) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 292) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 295) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 296) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 297) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 301) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 265) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 299) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 303) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 305) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 306) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 307) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 308) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 309) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 999) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10015) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10016) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10017) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10021) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10035) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10036) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10054) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10057) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10061) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10062) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10076) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10078) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10081) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10082) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10083) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10084) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10086) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10091) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10096) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10122) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10128) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10136) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10150) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10154) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10185) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10187) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10194) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10195) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10197) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10200) _
Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10201) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10204) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10220) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10231) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10235) Or (Trim(ActiveSheet.Cells(j, (FLAGCOLUMN)).Value) = 10255) _
Then
'turn white
ActiveSheet.Cells(j, 3).MergeArea.Interior.Color = rgbWhite
Else
'turn yellow
ActiveSheet.Cells(j, 3).MergeArea.Interior.Color = 10092543
End If
ActiveSheet.Cells(j, 2).Interior.Color = rgbWhite
ActiveSheet.Cells(j, 1).Interior.Color = rgbWhite
ActiveSheet.Cells(j, 7).Interior.Color = rgbWhite
ActiveSheet.Cells(j, 3).MergeArea.Borders(xlLeft).ColorIndex = rgbBlack
ActiveSheet.Cells(j, 1).Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(j, 3).MergeArea.Borders(xlRight).ColorIndex = rgbBlack
ActiveSheet.Cells(j, 1).Borders(xlRight).Weight = xlThin
ActiveSheet.Cells(j, 3).MergeArea.Borders(xlTop).ColorIndex = rgbBlack
ActiveSheet.Cells(j, 1).Borders(xlTop).Weight = xlThin
ActiveSheet.Cells(j, 3).MergeArea.Borders(xlBottom).ColorIndex = rgbBlack
ActiveSheet.Cells(j, 1).Borders(xlBottom).Weight = xlThin
ActiveSheet.Cells(j, 1).EntireRow.AutoFit
If ENGLISH_COL > 0 Then
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlRight).ColorIndex = rgbBlack
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlRight).Weight = xlThin
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlLeft).ColorIndex = rgbBlack
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlTop).ColorIndex = rgbBlack
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlTop).Weight = xlThin
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlBottom).ColorIndex = rgbBlack
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.Borders(xlBottom).Weight = xlThin
ActiveSheet.Cells(j, ENGLISH_COL).MergeArea.VerticalAlignment = xlVAlignTop
End If
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlRight).ColorIndex = rgbBlack
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlRight).Weight = xlThin
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlLeft).ColorIndex = rgbBlack
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlTop).ColorIndex = rgbBlack
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlTop).Weight = xlThin
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlBottom).ColorIndex = rgbBlack
ActiveSheet.Cells(j, FLAGCOLUMN).Borders(xlBottom).Weight = xlThin
ActiveSheet.Cells(j, FLAGCOLUMN).MergeArea.VerticalAlignment = xlVAlignTop
Else
If (j = DELETE_UNLOCK + 2) Then
ActiveSheet.Cells(DELETE_UNLOCK + 1, 1).EntireRow.Hidden = True
If (ActiveSheet.Cells(DELETE_UNLOCK + 3, 1).Value = "") Then
ActiveSheet.Cells(DELETE_UNLOCK + 3, 1).EntireRow.Hidden = True
End If
End If
ActiveSheet.Cells(j, 1).EntireRow.Hidden = True
Exit For
End If
Next j
End If
For i = HEADING_ROW + 1 To DELETE_GENERAL_QUERIES_END
If Trim(ActiveSheet.Cells(i, CONSULT_COL).Value) <> "" Then
If (ENGLISH_COL > 0) Then
If (Trim(ActiveSheet.Cells(i, ENGLISH_COL).Value) <> "") Then
EnglishConsultantFlags = EnglishConsultantFlags + 1
End If
If (ActiveSheet.Cells(i, CONSULT_COL).Font.Name = "MS PGothic") And Not (ActiveSheet.Cells(i, CONSULT_COL).Value Like ("*" + Chr(10))) Then
'Japanese font that has been pasted from PopUp window
If (Len(ActiveSheet.Cells(i, CONSULT_COL).Value) > 250) Then
ActiveSheet.Cells(i, CONSULT_COL).Value = ActiveSheet.Cells(i, CONSULT_COL).Value + Chr(10) + Chr(10)
Else
ActiveSheet.Cells(i, CONSULT_COL).Value = ActiveSheet.Cells(i, CONSULT_COL).Value + Chr(10)
End If
End If
End If
If (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 15) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 16) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 17) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 21) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 24) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 35) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 36) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 40) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 49) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 50) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 57) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 61) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 62) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 76) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 78) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 81) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 82) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 83) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 84) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 86) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 91) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 93) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 96) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 99) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 114) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 118) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 127) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 128) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 129) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 130) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 131) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 136) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 142) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 147) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 150) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 152) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 153) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 154) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 155) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 156) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 162) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 184) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 185) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 186) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 187) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 191) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 192) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 193) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 194) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 195) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 197) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 198) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 200) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 201) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 202) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 203) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 204) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 205) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 208) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 210) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 212) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 213) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 214) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 215) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 216) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 217) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 220) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 229) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 230) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 231) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 232) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 234) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 235) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 237) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 238) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 244) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 245) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 246) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 247) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 248) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 255) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 266) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 273) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 275) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 278) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 282) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 289) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 292) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 295) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 296) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 297) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 265) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 301) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 299) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 303) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 305) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 306) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 307) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 308) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 309) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 999) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10015) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10016) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10017) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10021) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10035) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10036) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10054) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10057) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10061) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10062) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10076) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10078) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10081) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10082) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10083) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10084) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10086) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10091) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10096) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10122) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10128) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10136) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10150) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10154) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10185) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10187) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10194) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10195) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10197) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10200) _
Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10201) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10204) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10220) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10231) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10235) Or (Trim(ActiveSheet.Cells(i, (FLAGCOLUMN)).Value) = 10255) _
Then
'turn white
ActiveSheet.Cells(i, CUSTOMER_COL).Interior.Color = rgbWhite
ActiveSheet.Cells(i, CUSTOMER_COL).Borders.LineStyle = xlContinuous
Else
'turn yellow
ActiveSheet.Cells(i, CUSTOMER_COL).Interior.Color = 10092543
ActiveSheet.Cells(i, CUSTOMER_COL).Borders.LineStyle = xlContinuous
End If
ActiveSheet.Cells(i, CONSULT_COL).Interior.Color = rgbWhite
If ENGLISH_COL > 0 Then
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Interior.Color = rgbWhite
ActiveSheet.Cells(i, ENGLISH_COL).Borders.LineStyle = xlContinuous
End If
ActiveSheet.Cells(i, FLAGCOLUMN).Interior.Color = rgbWhite
If (ENGLISH_COL > 0) Then
If (Not ActiveSheet.Cells(i, ENGLISH_COL).MergeCells) Then
Range(ActiveSheet.Cells(i, ENGLISH_COL), ActiveSheet.Cells(i, ENGLISH_COL + 3)).Merge
End If
End If
ActiveSheet.Cells(i, CONSULT_COL).Borders(xlLeft).ColorIndex = rgbBlack
ActiveSheet.Cells(i, CONSULT_COL).Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(i, CONSULT_COL).Borders(xlBottom).ColorIndex = rgbBlack
ActiveSheet.Cells(i, CONSULT_COL).Borders(xlBottom).Weight = xlThin
ActiveSheet.Cells(i, CUSTOMER_COL).Borders(xlRight).ColorIndex = rgbBlack
ActiveSheet.Cells(i, CUSTOMER_COL).Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(i, CUSTOMER_COL).Borders(xlLeft).ColorIndex = rgbBlack
ActiveSheet.Cells(i, CUSTOMER_COL).Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(i, CUSTOMER_COL).Borders(xlBottom).ColorIndex = rgbBlack
ActiveSheet.Cells(i, CUSTOMER_COL).Borders(xlBottom).Weight = xlThin
ActiveSheet.Cells(i, 1).EntireRow.AutoFit
If ENGLISH_COL > 0 Then
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlRight).ColorIndex = rgbBlack
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlRight).Weight = xlThin
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlLeft).ColorIndex = rgbBlack
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlTop).ColorIndex = rgbBlack
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlTop).Weight = xlThin
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlBottom).ColorIndex = rgbBlack
ActiveSheet.Cells(i, ENGLISH_COL).MergeArea.Borders(xlBottom).Weight = xlThin
End If
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlRight).ColorIndex = rgbBlack
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlRight).Weight = xlThin
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlLeft).ColorIndex = rgbBlack
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlLeft).Weight = xlThin
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlTop).ColorIndex = rgbBlack
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlTop).Weight = xlThin
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlBottom).ColorIndex = rgbBlack
ActiveSheet.Cells(i, FLAGCOLUMN).Borders(xlBottom).Weight = xlThin
ElseIf i < (DELETE_UNLOCK - 1) Then
'Removed consultant comment so we need to remove the flagid and shading
If ActiveSheet.Cells(i, CUSTOMER_COL).Interior.Color <> rgbBlack Then
ActiveSheet.Cells(i, CUSTOMER_COL).Interior.Color = rgbWhite
End If
ActiveSheet.Cells(i, CUSTOMER_COL).Borders.LineStyle = xlContinuous
ActiveSheet.Cells(i, CONSULT_COL).Borders.LineStyle = xlContinuous
If ENGLISH_COL > 0 Then
ActiveSheet.Cells(i, (ENGLISH_COL)).Value = ""
End If
ActiveSheet.Cells(i, (FLAGCOLUMN)).Value = ""
ActiveSheet.Cells(i, FLAGCOLUMN).Borders.LineStyle = xlLineStyleNone
End If
ActiveSheet.Cells(i, CONSULT_COL).WrapText = True
Next i
If (ENGLISH_COL > 0) And (EnglishConsultantFlags = 0) Then
'Multiple delete due to mergedness
ActiveSheet.Cells(i, ENGLISH_COL).EntireColumn.Delete Shift:=xlToLeft
ActiveSheet.Cells(i, ENGLISH_COL).EntireColumn.Delete Shift:=xlToLeft
ActiveSheet.Cells(i, ENGLISH_COL).EntireColumn.Delete Shift:=xlToLeft
ActiveSheet.Cells(i, ENGLISH_COL).EntireColumn.Delete Shift:=xlToLeft
End If
End If
End Sub
Private Sub SetVariables()
' Set the variables needed
Dim i As Integer
CONSULT_COL = 0
CUSTOMER_COL = 0
ENGLISH_COL = 0
DELETE_UNLOCK = 0
DELETE_GENERAL_QUERIES_END = 0
' Find columns and set column numbers
For i = 1 To COLUMN_NUMBERS
If ActiveSheet.Cells(HEADING_ROW, i).Value = CONSULT_TEXT Then
CONSULT_COL = i ' keep track of the column number for later use
End If
If ActiveSheet.Cells(HEADING_ROW, i).Value = CUSTOMER_TEXT Then
CUSTOMER_COL = i ' keep track of the column number for later use
End If
If ActiveSheet.Cells(HEADING_ROW, i).Value = ENGLISH_TEXT Then
ENGLISH_COL = i ' keep track of the column number for later use
End If
If CONSULT_COL > 0 And CUSTOMER_COL > 0 And ENGLISH_COL > 0 Then Exit For
Next i
'Set the flagID column dependant if the english column is still there or not
If ENGLISH_COL > 0 Then
FLAGCOLUMN = ENGLISH_COL + 4
Else
FLAGCOLUMN = CUSTOMER_COL + 1
End If
For i = LastRow("A", , ActiveSheet.Name) To HEADING_ROW Step -1
If ActiveSheet.Cells(i, 1).Value = UNLOCK_ROW Then
DELETE_UNLOCK = i ' keep track of the row number for later use
Exit For
End If
If ActiveSheet.Cells(i, 1).Value = "General Consultant Queries End" Then
DELETE_GENERAL_QUERIES_END = i
End If
Next i
End Sub
Private Sub HideEnglishLanguage()
' Find and hide English language Column
Dim i As Integer
If ENGLISH_COL > 0 Then
ActiveSheet.Cells(HEADING_ROW, ENGLISH_COL).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, ENGLISH_COL + 1).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, ENGLISH_COL + 2).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, ENGLISH_COL + 3).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, ENGLISH_COL + 4).EntireColumn.Hidden = True
Else
For i = 1 To COLUMN_NUMBERS
If ActiveSheet.Cells(HEADING_ROW, i).Value = ENGLISH_TEXT Then
ActiveSheet.Cells(HEADING_ROW, i).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, i + 1).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, i + 2).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, i + 3).EntireColumn.Hidden = True
ActiveSheet.Cells(HEADING_ROW, i + 4).EntireColumn.Hidden = True
ENGLISH_COL = i ' keep track of the column number for later use
Exit For
End If
Next i
End If
If FLAGCOLUMN > 0 Then
ActiveSheet.Cells(HEADING_ROW, FLAGCOLUMN).EntireColumn.Hidden = True
End If
End Sub
Private Sub RemoveConsultantFlagsUpwards()
' Find Consultant Flags to Delete (from the bottom of the worksheet up)
Dim theLastRow As Long
Dim i As Integer
Dim j As Integer ' blank row counter
Dim k As Integer
theLastRow = LastRow("A", , ActiveSheet.Name)
For i = theLastRow To HEADING_ROW Step -1
If ActiveSheet.Cells(i, 1).Value = DELETE_ROW Then
' delete all rows below this
ActiveSheet.Range(ActiveSheet.Cells(i, 1), ActiveSheet.Cells(theLastRow, 1)).EntireRow.Delete
Exit For
End If
Next i
End Sub
Private Sub RemoveConsultantFlagsDownwards()
' Find Consultant Flags to Delete (from the top of the worksheet down)
Dim theLastRow As Long
Dim i As Integer
theLastRow = LastRow("A", , ActiveSheet.Name)
For i = HEADING_ROW To theLastRow
If ActiveSheet.Cells(i, 1).Value = DELETE_ROW Then
' delete all rows below this
ActiveSheet.Range(ActiveSheet.Cells(i, 1), ActiveSheet.Cells(theLastRow, 1)).EntireRow.Delete
Exit For
End If
Next i
End Sub
Private Function LastRow(ColumnLetter As String, Optional StartingRow As Long = 0, Optional SheetName As String) As Long
' Return the last row of data in a column (can specify a row to start from)
If SheetName = "" Then SheetName = ActiveSheet.Name ' use SheetName or ActiveSheet
If StartingRow > 0 Then ' from the specified row down to last cell
LastRow = Sheets(SheetName).Range(ColumnLetter & StartingRow).End(xlDown).Row
Else ' from the bottom of sheet up to last cell
LastRow = Sheets(SheetName).Range(ColumnLetter & Rows.Count).End(xlUp).Row
End If
End Function
Sub Swap()
If ActiveSheet.ProtectContents Then
' ActiveSheet.Protect
Application.StatusBar = "Sheet is unprotected"
ActiveSheet.Unprotect Password:="asdfg"
Else
Application.StatusBar = "Sheet is protected"
ActiveSheet.Protect Password:="asdfg", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowInsertingColumns:=True
End If
End Sub
Sub ReRun()
If ActiveSheet.ProtectContents Then
'Unlock
Application.StatusBar = "Sheet is unprotected"
ActiveSheet.Unprotect Password:="asdfg"
CleanUpLOP
Else
CleanUpLOP
End If
End Sub
Sub Auto_Open()
Application.OnKey "^Q", "Swap"
Application.OnKey "^K", "ReRun"
End Sub
|
