Showing posts with label sum and count values on font color. Show all posts
Showing posts with label sum and count values on font color. Show all posts

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

Import data from SQL

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