Formatierung ist Datum / Standard / Datum. Wie geschrieben.
Tatsächlich ist das nur ein Ausschnitt (rot) von dem was VBA macht. Unten mal der Komplette Code.
Code:
Private Sub Worksheet_Change
If Target.Count > 1 Then Exit Sub
If Target.Row > 2 Then
If Target.Column = 13 Or Target.Column = 14 Then
Select Case Target.Column
Case 13
Target.Offset(, 2) = WorksheetFunction.Sum(Target, Target.Offset(, 1))
Case 14
Target.Offset(, 1) = WorksheetFunction.Sum(Target, Target.Offset(, -1))
Case Else
'nix machen
End Select
End If
End If
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("E3:AH44")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Value = UCase(.Value)
End If
End With
CleanUp:
Application.EnableEvents = True
If Target.Column = 12 And Target.Row >= 3 And Target.Count = 1 Then
If IsNumeric(Target.Offset(, -1)) Then
If Target.Value = 7.7 Then
Target = Target.Offset(, -1) * 1.077
ElseIf Target.Value = 19 Then
Target = Target.Offset(, -1) * 1.19
End If
Else
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
MsgBox "Es ist kein Zahlenwert in Spalte K."
End If
End If
If Target.Column = 7 And Target.Row > 3 Then
If UCase(Target) = "RE" Then
Application.EnableEvents = False
Target.Offset(, 1) = 83
Target.Offset(, 2) = Target.Offset(-1, 2) + 1
Target.Offset(, 3) = 1900
Application.EnableEvents = True
ElseIf UCase(Target) = "GU" Then
Application.EnableEvents = False
Target.Offset(, 1) = 83
Target.Offset(, 2) = Target.Offset(-1, 2)
Target.Offset(, 3) = Target.Offset(-1, 3) + 1
Target.Offset(, 4) = Target.Offset(-1, 4) * (-1)
Target.Offset(, 5) = Target.Offset(-1, 5) * (-1)
Target.Offset(, 6) = Date
Target.Offset(, 8) = Date
Target.Offset(, 12) = Date
Target.Offset(, 11) = Target.Offset(, 4)
Target.Offset(, 7) = Target.Offset(-1, 7)
Target.Offset(0, -5).Resize(1, 5).Value = Target.Offset(-1, -5).Resize(1, 5).Value
Target.Offset(1, -5).Resize(1, 5).Value = Target.Offset(-1, -5).Resize(1, 5).Value
Target.Offset(1) = "RE-02"
Target.Offset(1, 1) = 83
Target.Offset(1, 1) = Target.Offset(1, 1)
Target.Offset(1, 2) = Target.Offset(, 2)
Target.Offset(1, 3) = Target.Offset(, 3) + 1
Application.EnableEvents = True
End If
End If
If Not Target.Column = 5 Then Exit Sub
If Not Target.Row > 2 Then Exit Sub
Select Case LCase(Target.Value)
Case "hamu"
Me.Cells(Target.Row, 6).Value = "BP"
Case "cwi", "mass", "casc", "scbe"
Me.Cells(Target.Row, 6).Value = "BS"
Case "unul", "wert"
Me.Cells(Target.Row, 6).Value = "MUE"
Case "spt"
Me.Cells(Target.Row, 6).Value = "intern"
Case Else
Me.Cells(Target.Row, 6).Value = "XXX"
End Select
If Not Target.Column = 5 Then Exit Sub
If Not Target.Row > 2 Then Exit Sub
Dim tRow As Long
tRow = Target.Row
Select Case LCase(Target.Value)
Case "hamu"
Me.Cells(tRow, 22).Value = "hafu@gruner.eu"
Case "cwi"
Me.Cells(tRow, 22).Value = "cwi@gruner.eu"
Case "casc"
Me.Cells(tRow, 22).Value = "casc@gruner.eu"
Case "mass"
Me.Cells(tRow, 22).Value = "mass@gruner.eu"
Case "unul"
Me.Cells(tRow, 22).Value = "unul@gruner.eu"
Case "spt"
Me.Cells(tRow, 22).Value = "spt@gruner.eu"
End Select
If Target.Count > 1 Then Exit Sub
If Target.Row > 2 Then
If Target.Column = 13 Or Target.Column = 14 Then
Select Case Target.Column
Case 13
Target.Offset(, 2) = WorksheetFunction.Sum(Target, Target.Offset(, 1))
Case 14
Target.Offset(, 1) = WorksheetFunction.Sum(Target, Target.Offset(, -1))
Case Else
'nix machen
End Select
End If
End If
End Sub