Paul,

Thanks for that code. Along with your code, I added the following lines
to display the result

Range("A2").Value = c
Range("A3").Value = sm

Output was 0 and 0. I dont know why this happens.. Any idea? Please
clarify.

Thanks,
Thulasiram

PCLIVE (RemoveThis) wrote:
One way:

Sub test()
Dim rng As Range
c = 0
sm = 0
Set rng = Range("A1:Z1")

For Each cell In rng
If cell.Interior.ColorIndex = 3 _
Then
c = c + 1
sm = sm + cell.Value
Else:
End If
Next cell
End Sub


Regards,
Paul

"Sjakkie" wrote in message
...
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