Would anyone like to tell me how it would be possible to
add only the cells that are colored e.g. red through a range

Function SumRed(Target As Range) As Integer
Dim c As Range

For Each c In Target
If c.Interior.ColorIndex = 3 Then SumRed = SumRed + 1
Next c
End Function

HTH,
Merjet