=2*AGGREGAT(9;;Z1S:Z(-1)S)-SUMME(Z1S:Z(-1)S)dim Zelle as Range
dim Zeile as long
for each Zelle in ActiveSheet.usedrange.Columns(?) 'spaltennummer der Spalte mit den Einzelpreisen
if Zelle.Interior.Color = ??? 'RGB-Wert der Farbe violett Then
Zeile = Zelle.Row + 1
elseif Zelle.Interior.Color = "??? 'RGB-Wert der Farbe Rot
Zelle.FormulaR1C1 = "=Sum(R" & Zeile & "C:R[1]C)"
else
end if
nextSub Unit()
Dim Y As Range
With Range("V:V").ColumnDifferences(Range("V:V").Find("Gpreis_Rest", LookIn:=xlValues, lookat:=xlWhole))
For Each Y In .Areas
Y.Offset(0, 0).Cells(Y.Cells.Count, 1) = Application.Sum(Y.Resize(Y.Cells.Count - 1))
Next
End With
End Sub
Sub Summe()
Application.ScreenUpdating = True
Dim Zelle As Range
Dim Zeile As Long
Zeile = 0
For Each Zelle In ActiveSheet.UsedRange.Columns("V").Cells
If Zelle.Interior.Color = RGB(112, 48, 160) Then
Zeile = Zelle.Row + 1
ElseIf Zelle.Interior.Color = RGB(218, 150, 148) Then
Zelle.Formula = "=SUM(V" & Zeile & ":V" & Zelle.Row & ")"
End If
Next Zelle
End SubSub test()
Dim rn As Range
For Each rn In Columns("U:U").SpecialCells(xlCellTypeBlanks).Areas
If rn.Row > 2 Then
Debug.Print rn.Offset(0, 1).Address(0, 0)
rn(1).Offset(-1, 1).Formula = "=Sum(" & rn.Offset(0, 1).Address(0, 0) & ")"
End If
Next
End Sub