Summing based on cell color

[riksutin]

Hi!

I wanted to know how I can sum up values in a cell based on it's interior color?
So I have filled in the color for these cells (range F4:F67):

And then I would like the worksheet tab to sum up the values here based on the colors, so if I change the color of one of the cells, it automatically calculates the value to the right PL (A73:A77)

 

[kweaver]

Read this material by the late (and great) Chip Pearson:

Color Functions In Excel

 

[lrobbo314]

You would need to change your cell references. Can't tell exactly where your data is in the pictures. But this seems to work.

Book3
	A	B	C	D
1	328
2	2,161		PL1	8,313
3	2,342		PL2	10,630
4	1,253		PL3	14,689
5	2,229		PL4	11,847
6	3,047
7	1,232
8	2,256
9	2,202
10	1,893
11	3,029
12	2,547
13	3,066
14	1,779
15	1,868
16	3,304
17	1,333
18	2,815
19	3,358
20	1,037
21	2,400
Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range:     Set a = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)

If Not Intersect(a, Target) Is Nothing Then

    Dim r As Range:     Set r = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Dim SD As Object:   Set SD = CreateObject("Scripting.Dictionary")
    Dim c As Range
    
    For Each c In r
        SD(c.Interior.Color) = SD(c.Interior.Color) + c.Value
    Next c
    
    Set r = Range("C2").Resize(SD.Count)
    
    r.Value = Evaluate("""PL""&" & "Row(" & r.Address & ")-1")
    Set r = r.Offset(, 1)
    r.Value = Application.Transpose(SD.items)
    
    For i = 0 To SD.Count - 1
        r.Cells(i + 1).Interior.Color = SD.keys()(i)
    Next i

End If
End Sub