formule maar even gekopieerd
Private Sub Worksheet_Change(ByVal Target As Range)
lts = Range("A65536").End(xlUp).Row
pr = ActiveCell.Address
C = ActiveCell.Column
R = Target.Row
For i = 9 To lts Step 5
If Target.Address = "$A$" & i Then GoTo tweede:
If Target.Address = "$D$" & i Then GoTo derde:
If Target.Address = "$F$" & i Then GoTo derde:
If Target.Address = "$H$" & i Then GoTo derde:
If Target.Address = "$J$" & i Then GoTo derde:
If Target.Address = "$L$" & i Then GoTo derde:
If Target.Address = "$N$" & i Then GoTo derde:
If Target.Address = "$P$" & i Then GoTo derde:
If Target.Address = "$R$" & i Then GoTo derde:
If Target.Address = "$T$" & i Then GoTo derde:
If Target.Address = "$V$" & i Then GoTo derde:
If Target.Address = "$X$" & i Then GoTo derde:
If Target.Address = "$Z$" & i Then GoTo derde:
If Target.Address = "$AB$" & i Then GoTo derde:
If Target.Address = "$AD$" & i Then GoTo derde:
If Target.Address = "$AF$" & i Then GoTo derde:
If Target.Address = "$AH$" & i Then GoTo derde:
If Target.Address = "$AJ$" & i Then GoTo derde:
If Target.Address = "$AL$" & i Then GoTo derde:
If Target.Address = "$AN$" & i Then GoTo derde:
If Target.Address = "$AP$" & i Then GoTo derde:
If Target.Address = "$AR$" & i Then GoTo derde:
If Target.Address = "$AT$" & i Then GoTo derde:
If Target.Address = "$AV$" & i Then GoTo derde:
If Target.Address = "$AX$" & i Then GoTo derde:
If Target.Address = "$AZ$" & i Then GoTo derde:
If Target.Address = "$BB$" & i Then GoTo derde:
If Target.Address = "$BD$" & i Then GoTo derde:
If Target.Address = "$BF$" & i Then GoTo derde:
Next
Exit Sub
tweede:
Range("B" & i).FormulaR1C1 = "=VLOOKUP(RC[-1],Objecten!C:C[7],7,FALSE)"
Range("C" & i).FormulaR1C1 = "=VLOOKUP(RC[-2],Objecten!C[-1]:C[6],8,FALSE)"
Range("A" & i + 1) = "=TEXT(R[-1]C[1],""u:mm"") & "" - "" & TEXT(R[-1]C[2],""u:mm"") & "" uur"""
GoTo Uitrekenen
derde:
Cells(R + 1, C).FormulaR1C1 = "=VLOOKUP(R[-1]C,Personeel!C2:C9,8,FALSE)"
Cells(R + 2, C).FormulaR1C1 = "=VLOOKUP(R[-2]C1,Objecten!C2:C9,7,FALSE)"
Cells(R + 2, C + 1).FormulaR1C1 = "=VLOOKUP(R[-2]C1,Objecten!C2:C9,8,FALSE)"
Cells(R + 4, C).FormulaR1C1 = "=((R[-2]C[1]+(R[-2]C[1]<R[-2]C))-R[-2]C)*24"
Cells(R + 3, C).FormulaR1C1 = "=IF(AND(R[-3]C<>""Lege Dienst"",R[1]C>0),"""",R[1]C)"
If Cells(R, C) = "Lege dienst" Then GoTo rood
If Cells(R, C) <> "Lege dienst" Then GoTo grijs
rood:
Cells(R, C).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
GoTo Uitrekenen
grijs:
Cells(R, C).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
.PatternTintAndShade = 0
End With
GoTo Uitrekenen
'uitrekenen totalen
Uitrekenen:
For R = 13 To lts Step 5
Range("BH" & R).FormulaR1C1 = "=SUM(RC[-56]:RC[-1])"
Range("BI" & R - 1).FormulaR1C1 = "=SUM(RC[-57]:RC[-2])"
Range("D2").FormulaR1C1 = "=SUM(C[56])"
Range("D3").FormulaR1C1 = "=SUM(C[57])"
Next
hc = ActiveCell
If Range("D3") > 0 Then GoTo rode
If Range("D3") >= 0 Then GoTo groene
rode:
Range("D3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(pr).Select
Exit Sub
groene:
Range("D3").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
.PatternTintAndShade = 0
End With
Range(pr).Select
Exit Sub
leeg:
End Sub