Sunday, February 20, 2011

Sum and Count Values on Font Color

Suppose you have a data like below





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

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...