Pages

Monday, February 21, 2011

Sum and Count Values on Interior Color ( Fill Color)

Suppose you want to add the values on the basis of fill or background color like below



Here is the code :-

Sub sum_colored_values()
Dim i, j, k, l As Long
Dim sm1, sm2, sm3 As Long

For i = 2 To Sheets(1).Range("a1").End(xlDown).Row

If Sheets(1).Cells(i, 1).Interior.Color = Sheets(1).Cells(9, 5).Interior.Color Then

k = k + 1
sm1 = sm1 + Sheets(1).Cells(i, 2).Value
End If

If Sheets(1).Cells(i, 1).Interior.Color = Sheets(1).Cells(10, 5).Interior.Color Then

j = j + 1
sm2 = sm2 + Sheets(1).Cells(i, 2).Value
End If

If Sheets(1).Cells(i, 1).Interior.Color = Sheets(1).Cells(11, 5).Interior.Color Then

l = l + 1
sm3 = sm3 + Sheets(1).Cells(i, 2).Value
End If


Next i
Sheets(1).Cells(9, 6).Value = sm1

Sheets(1).Cells(9, 7).Value = k
Sheets(1).Cells(10, 6).Value = sm2

Sheets(1).Cells(10, 7).Value = j

Sheets(1).Cells(11, 6).Value = sm3

Sheets(1).Cells(11, 7).Value = l

End Sub

No comments:

Post a Comment