Sub SBZ()
Application.ScreenUpdating = False
Range("B2").Select
ActiveWindow.FreezePanes = True
Range("B:B,F:F,H:H").NumberFormat = "dd/mm/yy;@"
Range("C:C,G:G,I:I").NumberFormat = "h:mm:ss;@"
Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("L:L").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "=(RC[-4]+RC[-3]-(RC[-6]+RC[-5]))*24*60"
Columns("L:L").NumberFormat = "General"
Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=30"
Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Count).SetFirstPriority
With Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).StopIfTrue = False
Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=30"
Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Count).SetFirstPriority
With Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Range("L2:L" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).StopIfTrue = False
Range("L1") = "Arb.Zeit"
Range("M1") = "SBZ Zeit"
Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = "=(RC[-5]+RC[-4]-(RC[-11]+RC[-10]))*24*60"
Columns("M:M").NumberFormat = "General"
Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=240"
Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Count).SetFirstPriority
With Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).StopIfTrue = False
Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=240"
Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions.Count).SetFirstPriority
With Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row).FormatConditions(1).StopIfTrue = False
Cells.Borders(xlDiagonalDown).LineStyle = xlNone
Cells.Borders(xlDiagonalUp).LineStyle = xlNone
With Cells.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Cells.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Cells.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Cells.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Cells.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Cells.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub