Advertisement
Guest User

Untitled

a guest
Jan 3rd, 2019
78
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
VBScript 14.99 KB | None | 0 0
  1. Private Sub Workbook_BeforePrint(Cancel As Boolean)
  2. Dim nm As Name
  3. For Each nm In ActiveWorkbook.Names
  4. On Error Resume Next
  5.  nm.Delete
  6.  Next
  7. Call set_red_formatting
  8. 'ActiveSheet.PageSetup.PrintArea = "=OFFSET(Handover!$A$1,0,0,COUNTA(Handover!$A:$A),22)"
  9. ActiveWorkbook.Names.Add Name:="Print_Area", RefersTo:="=OFFSET(Handover!$A$1,0,0,COUNTA(Handover!$A:$A),22)"
  10. End Sub
  11. Sub set_red_formatting()
  12.     Application.ScreenUpdating = False
  13.     ActiveWindow.FreezePanes = False
  14.     Cells.FormatConditions.Delete
  15.     Range("A1:D1").Select
  16.     Selection.UnMerge
  17.     Range("A1:AZ2").Select
  18.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
  19.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  20.     With Selection.FormatConditions(1).Interior
  21.         .PatternColorIndex = xlAutomatic
  22.         .ThemeColor = xlThemeColorDark1
  23.         .TintAndShade = 0
  24.     End With
  25.     Selection.FormatConditions(1).StopIfTrue = False
  26.     Range("A1:A200").Select
  27.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  28.         "=IF(LEN($A1)>0,1,0)"
  29.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  30.     With Selection.FormatConditions(1).Interior
  31.         .PatternColorIndex = xlAutomatic
  32.         .ThemeColor = xlThemeColorAccent2
  33.         .TintAndShade = 0.599963377788629
  34.     End With
  35.     Selection.FormatConditions(1).StopIfTrue = False
  36.     Range("S1:S200").Select
  37.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  38.         "=IF($S1=""2P"",1,0)"
  39.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  40.     With Selection.FormatConditions(1).Font
  41.         .Color = -16776961
  42.         .TintAndShade = 0
  43.     End With
  44.     With Selection.FormatConditions(1).Interior
  45.         .PatternColorIndex = xlAutomatic
  46.         .Color = 15198201
  47.         .TintAndShade = 0
  48.     End With
  49.     Selection.FormatConditions(1).StopIfTrue = False
  50.     Range("A3:V200").Select
  51.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  52.         "=IF($G3=""Discharge"",1,0)"
  53.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  54.     With Selection.FormatConditions(1).Interior
  55.         .PatternColorIndex = xlAutomatic
  56.         .ThemeColor = xlThemeColorDark1
  57.         .TintAndShade = -0.14996795556505
  58.     End With
  59.     Selection.FormatConditions(1).StopIfTrue = False
  60.     Range("T1:U200").Select
  61.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  62.         "=IF(LEN(T1)>0,1,0)"
  63.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  64.     With Selection.FormatConditions(1).Interior
  65.         .PatternColorIndex = xlAutomatic
  66.         .Color = 15138815
  67.         .TintAndShade = 0
  68.     End With
  69.     Selection.FormatConditions(1).StopIfTrue = False
  70.     Range("I3:I200").Select
  71.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  72.         "=LEN(TRIM(A1))=0"
  73.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  74.     With Selection.FormatConditions(1).Interior
  75.         .PatternColorIndex = xlAutomatic
  76.         .ThemeColor = xlThemeColorDark1
  77.         .TintAndShade = 0
  78.     End With
  79.     Selection.FormatConditions(1).StopIfTrue = False
  80.     Range("K3:K200").Select
  81.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
  82.         Formula1:="=1", Formula2:="=6"
  83.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  84.     With Selection.FormatConditions(1).Interior
  85.         .PatternColorIndex = xlAutomatic
  86.         .Color = 5296274
  87.         .TintAndShade = 0
  88.     End With
  89.     Selection.FormatConditions(1).StopIfTrue = False
  90.     Range("K3:K200").Select
  91.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
  92.         Formula1:="=7", Formula2:="=11"
  93.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  94.     With Selection.FormatConditions(1).Interior
  95.         .PatternColorIndex = xlAutomatic
  96.         .Color = 49407
  97.         .TintAndShade = 0
  98.     End With
  99.     Selection.FormatConditions(1).StopIfTrue = False
  100.     Range("K3:K200").Select
  101.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
  102.         Formula1:="=11"
  103.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  104.     With Selection.FormatConditions(1).Interior
  105.         .PatternColorIndex = xlAutomatic
  106.         .Color = 255
  107.         .TintAndShade = 0
  108.     End With
  109.     Selection.FormatConditions(1).StopIfTrue = False
  110.     Range("K3:K200").Select
  111.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
  112.         Formula1:="=1", Formula2:="=6"
  113.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  114.     With Selection.FormatConditions(1).Interior
  115.         .PatternColorIndex = xlAutomatic
  116.         .Color = 5296274
  117.         .TintAndShade = 0
  118.     End With
  119.     Selection.FormatConditions(1).StopIfTrue = False
  120.     Range("K3:K200").Select
  121.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
  122.         Formula1:="=7", Formula2:="=10"
  123.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  124.     With Selection.FormatConditions(1).Interior
  125.         .PatternColorIndex = xlAutomatic
  126.         .Color = 49407
  127.         .TintAndShade = 0
  128.     End With
  129.     Selection.FormatConditions(1).StopIfTrue = False
  130.     Range("K3:K200").Select
  131.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
  132.         Formula1:="=11"
  133.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  134.     With Selection.FormatConditions(1).Interior
  135.         .PatternColorIndex = xlAutomatic
  136.         .Color = 255
  137.         .TintAndShade = 0
  138.     End With
  139.     Selection.FormatConditions(1).StopIfTrue = False
  140.     Range("K3:K200").Select
  141.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
  142.         Formula1:="="""""
  143.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  144.     With Selection.FormatConditions(1).Font
  145.         .ThemeColor = xlThemeColorDark1
  146.         .TintAndShade = 0
  147.     End With
  148.     With Selection.FormatConditions(1).Interior
  149.         .PatternColorIndex = xlAutomatic
  150.         .ThemeColor = xlThemeColorDark1
  151.         .TintAndShade = 0
  152.     End With
  153.     Selection.FormatConditions(1).StopIfTrue = False
  154.     Range("I3:I200").Select
  155.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:="="""""
  156.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  157.     With Selection.FormatConditions(1).Interior
  158.         .Pattern = xlNone
  159.         .TintAndShade = 0
  160.     End With
  161.     Selection.FormatConditions(1).StopIfTrue = False
  162.     Range("I3:I200").Select
  163.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
  164.         Formula1:="=0", Formula2:="=10"
  165.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  166.     With Selection.FormatConditions(1).Interior
  167.         .PatternColorIndex = xlAutomatic
  168.         .ThemeColor = xlThemeColorAccent6
  169.         .TintAndShade = 0.599963377788629
  170.     End With
  171.     Selection.FormatConditions(1).StopIfTrue = False
  172.     Range("L3:L200").Select
  173.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
  174.         Formula1:="=0"
  175.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  176.     With Selection.FormatConditions(1).Interior
  177.         .PatternColorIndex = xlAutomatic
  178.         .Color = 255
  179.         .TintAndShade = 0
  180.     End With
  181.     Selection.FormatConditions(1).StopIfTrue = False
  182.     Range("L3:L200").Select
  183.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
  184.         Formula1:="=3", Formula2:="=4"
  185.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  186.     With Selection.FormatConditions(1).Interior
  187.         .PatternColorIndex = xlAutomatic
  188.         .Color = 49407
  189.         .TintAndShade = 0
  190.     End With
  191.     Selection.FormatConditions(1).StopIfTrue = False
  192.     Range("L3:L200").Select
  193.     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
  194.         Formula1:="=1", Formula2:="=2"
  195.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  196.     With Selection.FormatConditions(1).Interior
  197.         .PatternColorIndex = xlAutomatic
  198.         .Color = 5296274
  199.         .TintAndShade = 0
  200.     End With
  201.     Selection.FormatConditions(1).StopIfTrue = False
  202.     Range("G1:G200").Select
  203.     Selection.FormatConditions.Add Type:=xlTextString, String:="EDM", _
  204.         TextOperator:=xlContains
  205.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  206.     With Selection.FormatConditions(1).Interior
  207.         .PatternColorIndex = xlAutomatic
  208.         .Color = 6750207
  209.         .TintAndShade = 0
  210.     End With
  211.     Selection.FormatConditions(1).StopIfTrue = False
  212.     Range("G1:G200").Select
  213.     Selection.FormatConditions.Add Type:=xlTextString, String:="FIRST HV", _
  214.         TextOperator:=xlContains
  215.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  216.     With Selection.FormatConditions(1).Interior
  217.         .PatternColorIndex = xlAutomatic
  218.         .Color = 6750207
  219.         .TintAndShade = 0
  220.     End With
  221.     Selection.FormatConditions(1).StopIfTrue = False
  222.     Range("G1:G200").Select
  223.     Selection.FormatConditions.Add Type:=xlTextString, String:="Assessment", _
  224.         TextOperator:=xlContains
  225.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  226.     With Selection.FormatConditions(1).Interior
  227.         .PatternColorIndex = xlAutomatic
  228.         .Color = 6750207
  229.         .TintAndShade = 0
  230.     End With
  231.     Selection.FormatConditions(1).StopIfTrue = False
  232.     Range("A2:AZ2").Select
  233.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
  234.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  235.     With Selection.FormatConditions(1).Interior
  236.         .Pattern = xlNone
  237.         .TintAndShade = 0
  238.     End With
  239.     Selection.FormatConditions(1).StopIfTrue = False
  240.     Range("T1:U1").Select
  241.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
  242.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  243.     With Selection.FormatConditions(1).Interior
  244.         .Pattern = xlNone
  245.         .TintAndShade = 0
  246.     End With
  247.     Selection.FormatConditions(1).StopIfTrue = False
  248.     Range("G1:G200").Select
  249.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  250.         "=IF($G1=""Treatment"",1,0)"
  251.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  252.     With Selection.FormatConditions(1).Interior
  253.         .PatternColorIndex = xlAutomatic
  254.         .Color = 15987707
  255.         .TintAndShade = 0
  256.     End With
  257.     Selection.FormatConditions(1).StopIfTrue = False
  258.     Range("K1:K200").Select
  259.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  260.         "=IF($G1=""Discharge"",IF($K1>11,1,0),0)"
  261.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  262.     With Selection.FormatConditions(1).Interior
  263.         .Pattern = xlUp
  264.         .PatternColor = 9013673
  265.         .Color = 255
  266.         .TintAndShade = 0
  267.         .PatternTintAndShade = 0
  268.     End With
  269.     Selection.FormatConditions(1).StopIfTrue = False
  270.     Range("K1:K200").Select
  271.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  272.         "=IF($G1=""Discharge"",IF(AND($K1>6,$K1<12),1,0),0)"
  273.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  274.     With Selection.FormatConditions(1).Interior
  275.         .Pattern = xlUp
  276.         .PatternThemeColor = xlThemeColorDark1
  277.         .Color = 49407
  278.         .TintAndShade = 0
  279.         .PatternTintAndShade = -0.499984740745262
  280.     End With
  281.     Selection.FormatConditions(1).StopIfTrue = False
  282.     Range("K1:K200").Select
  283.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  284.         "=IF($G1=""Discharge"",IF($K1<7,1,0),0)"
  285.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  286.     With Selection.FormatConditions(1).Interior
  287.         .Pattern = xlUp
  288.         .PatternThemeColor = xlThemeColorDark1
  289.         .Color = 5296274
  290.         .TintAndShade = 0
  291.         .PatternTintAndShade = -0.499984740745262
  292.     End With
  293.     Selection.FormatConditions(1).StopIfTrue = False
  294.     Range("K1:K200").Select
  295.     Selection.FormatConditions.Add Type:=xlTextString, String:="~?", _
  296.         TextOperator:=xlContains
  297.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  298.     With Selection.FormatConditions(1).Font
  299.         .Bold = True
  300.         .Italic = False
  301.         .TintAndShade = 0
  302.     End With
  303.     With Selection.FormatConditions(1).Interior
  304.         .Pattern = xlDown
  305.         .PatternThemeColor = xlThemeColorAccent4
  306.         .ThemeColor = xlThemeColorAccent4
  307.         .TintAndShade = 0.399884029663991
  308.         .PatternTintAndShade = 0.599963377788629
  309.     End With
  310.     Selection.FormatConditions(1).StopIfTrue = False
  311.     Range("T1:U200").Select
  312.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  313.         "=IF($G1=""Discharge"",IF(LEN(T1)>1,1,0),0)"
  314.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  315.     With Selection.FormatConditions(1).Interior
  316.         .Pattern = xlUp
  317.         .PatternThemeColor = xlThemeColorDark1
  318.         .Color = 13434879
  319.         .TintAndShade = 0
  320.         .PatternTintAndShade = -0.249946592608417
  321.     End With
  322.     Selection.FormatConditions(1).StopIfTrue = False
  323.     Range("A1:A200").Select
  324.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
  325.         "=IF($G1=""Discharge"",IF(LEN($A1)>1,1,0),0)"
  326.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  327.     With Selection.FormatConditions(1).Interior
  328.         .Pattern = xlUp
  329.         .PatternThemeColor = xlThemeColorDark1
  330.         .Color = 14869228
  331.         .TintAndShade = 0
  332.         .PatternTintAndShade = -0.249946592608417
  333.     End With
  334.     Selection.FormatConditions(1).StopIfTrue = False
  335.     Range("A2:V2").Select
  336.     Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
  337.     Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  338.     With Selection.FormatConditions(1).Interior
  339.         .Pattern = xlCrissCross
  340.         .PatternThemeColor = xlThemeColorDark1
  341.         .ThemeColor = xlThemeColorDark1
  342.         .TintAndShade = -0.14996795556505
  343.         .PatternTintAndShade = -4.99893185216834E-02
  344.     End With
  345.     Selection.FormatConditions(1).StopIfTrue = False
  346.     Range("A1:D1").Select
  347.     Selection.Merge
  348.     With Selection
  349.         .HorizontalAlignment = xlLeft
  350.         .VerticalAlignment = xlTop
  351.         .WrapText = False
  352.         .Orientation = 0
  353.         .AddIndent = False
  354.         .IndentLevel = 0
  355.         .ShrinkToFit = False
  356.         .ReadingOrder = xlContext
  357.         .MergeCells = True
  358.     End With
  359.     Range("E3").Select
  360.     ActiveWindow.FreezePanes = True
  361.     Application.ScreenUpdating = True
  362. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement