Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Private Sub Workbook_BeforePrint(Cancel As Boolean)
- Dim nm As Name
- For Each nm In ActiveWorkbook.Names
- On Error Resume Next
- nm.Delete
- Next
- Call set_red_formatting
- 'ActiveSheet.PageSetup.PrintArea = "=OFFSET(Handover!$A$1,0,0,COUNTA(Handover!$A:$A),22)"
- ActiveWorkbook.Names.Add Name:="Print_Area", RefersTo:="=OFFSET(Handover!$A$1,0,0,COUNTA(Handover!$A:$A),22)"
- End Sub
- Sub set_red_formatting()
- Application.ScreenUpdating = False
- ActiveWindow.FreezePanes = False
- Cells.FormatConditions.Delete
- Range("A1:D1").Select
- Selection.UnMerge
- Range("A1:AZ2").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("A1:A200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF(LEN($A1)>0,1,0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent2
- .TintAndShade = 0.599963377788629
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("S1:S200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($S1=""2P"",1,0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Font
- .Color = -16776961
- .TintAndShade = 0
- End With
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 15198201
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("A3:V200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($G3=""Discharge"",1,0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = -0.14996795556505
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("T1:U200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF(LEN(T1)>0,1,0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 15138815
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("I3:I200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=LEN(TRIM(A1))=0"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K3:K200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
- Formula1:="=1", Formula2:="=6"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 5296274
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K3:K200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
- Formula1:="=7", Formula2:="=11"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 49407
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K3:K200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
- Formula1:="=11"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 255
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K3:K200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
- Formula1:="=1", Formula2:="=6"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 5296274
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K3:K200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
- Formula1:="=7", Formula2:="=10"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 49407
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K3:K200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
- Formula1:="=11"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 255
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K3:K200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
- Formula1:="="""""
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Font
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = 0
- End With
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("I3:I200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:="="""""
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlNone
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("I3:I200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
- Formula1:="=0", Formula2:="=10"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .ThemeColor = xlThemeColorAccent6
- .TintAndShade = 0.599963377788629
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("L3:L200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
- Formula1:="=0"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 255
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("L3:L200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
- Formula1:="=3", Formula2:="=4"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 49407
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("L3:L200").Select
- Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
- Formula1:="=1", Formula2:="=2"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 5296274
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("G1:G200").Select
- Selection.FormatConditions.Add Type:=xlTextString, String:="EDM", _
- TextOperator:=xlContains
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 6750207
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("G1:G200").Select
- Selection.FormatConditions.Add Type:=xlTextString, String:="FIRST HV", _
- TextOperator:=xlContains
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 6750207
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("G1:G200").Select
- Selection.FormatConditions.Add Type:=xlTextString, String:="Assessment", _
- TextOperator:=xlContains
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 6750207
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("A2:AZ2").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlNone
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("T1:U1").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlNone
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("G1:G200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($G1=""Treatment"",1,0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .PatternColorIndex = xlAutomatic
- .Color = 15987707
- .TintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K1:K200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($G1=""Discharge"",IF($K1>11,1,0),0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlUp
- .PatternColor = 9013673
- .Color = 255
- .TintAndShade = 0
- .PatternTintAndShade = 0
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K1:K200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($G1=""Discharge"",IF(AND($K1>6,$K1<12),1,0),0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlUp
- .PatternThemeColor = xlThemeColorDark1
- .Color = 49407
- .TintAndShade = 0
- .PatternTintAndShade = -0.499984740745262
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K1:K200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($G1=""Discharge"",IF($K1<7,1,0),0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlUp
- .PatternThemeColor = xlThemeColorDark1
- .Color = 5296274
- .TintAndShade = 0
- .PatternTintAndShade = -0.499984740745262
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("K1:K200").Select
- Selection.FormatConditions.Add Type:=xlTextString, String:="~?", _
- TextOperator:=xlContains
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Font
- .Bold = True
- .Italic = False
- .TintAndShade = 0
- End With
- With Selection.FormatConditions(1).Interior
- .Pattern = xlDown
- .PatternThemeColor = xlThemeColorAccent4
- .ThemeColor = xlThemeColorAccent4
- .TintAndShade = 0.399884029663991
- .PatternTintAndShade = 0.599963377788629
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("T1:U200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($G1=""Discharge"",IF(LEN(T1)>1,1,0),0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlUp
- .PatternThemeColor = xlThemeColorDark1
- .Color = 13434879
- .TintAndShade = 0
- .PatternTintAndShade = -0.249946592608417
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("A1:A200").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
- "=IF($G1=""Discharge"",IF(LEN($A1)>1,1,0),0)"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlUp
- .PatternThemeColor = xlThemeColorDark1
- .Color = 14869228
- .TintAndShade = 0
- .PatternTintAndShade = -0.249946592608417
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("A2:V2").Select
- Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
- Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
- With Selection.FormatConditions(1).Interior
- .Pattern = xlCrissCross
- .PatternThemeColor = xlThemeColorDark1
- .ThemeColor = xlThemeColorDark1
- .TintAndShade = -0.14996795556505
- .PatternTintAndShade = -4.99893185216834E-02
- End With
- Selection.FormatConditions(1).StopIfTrue = False
- Range("A1:D1").Select
- Selection.Merge
- With Selection
- .HorizontalAlignment = xlLeft
- .VerticalAlignment = xlTop
- .WrapText = False
- .Orientation = 0
- .AddIndent = False
- .IndentLevel = 0
- .ShrinkToFit = False
- .ReadingOrder = xlContext
- .MergeCells = True
- End With
- Range("E3").Select
- ActiveWindow.FreezePanes = True
- Application.ScreenUpdating = True
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement