here is a function i found a while back


Function Count_By_Color(rColor As Range, rCountRange As Range)

Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rCountRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell

Count_By_Color = vResult

End Function

"Thulasiram" wrote:

Hello All,

I have a question on counting and sum the cells in a range that has
color/conditional formatting.

For example, I have

Dim rng as Range
rng = Range ("A1:Z1")

Assuming that cells B1 (value = 100), D1 (value = 200), F1(value = 340)
have color formatting (colorindex = 3) in the 'rng',

the number of highlighted cells is 3.
the sum of the highlighted celss = 100+200+340 = 640.

Please help me translate into a VBA code.

For each cell in rng
if cell.interior.colorindex = 3 then

count = "number of highlighted cells in the range" ---- how to
translate this line into a VBA code?
'for this case, the output would be count = 3

sum = "the sum of the values in the highlighted cells" ---- how to
translate this line into a VBA code?
'for this case, the output would be sum = 640


end if
next cell

Thanks for your help,
-Thulasiram