
you want to sum and count the data on the basis of font color in the below table

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).Font.Color = Sheets(1).Cells(9, 5).Font.Color Then
k = k + 1
sm1 = sm1 + Sheets(1).Cells(i, 2).Value
End If
If Sheets(1).Cells(i, 1).Font.Color = Sheets(1).Cells(10, 5).Font.Color Then
j = j + 1
sm2 = sm2 + Sheets(1).Cells(i, 2).Value
End If
If Sheets(1).Cells(i, 1).Font.Color = Sheets(1).Cells(11, 5).Font.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