Count and Sum the cells with color formatting

[Thulasiram]

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

 

[Sjakkie]

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

 

[PCLIVE]

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

 

[Thulasiram]

Sjakkie,

Thanks for giving that code . I did the following with that function. I
clicked a cell A2 and entered

=Count_By_Color(A1:Z1)

It returned a #value!.

To suit my need, I wanted to interpret that as a worksheet change
routine and not as a function. So, I modified the code that you gave
like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A1:Z1")

Dim vResult As Integer

For Each Cell In rng
If Cell.Interior.ColorIndex = 3 Then
vResult = vResult + 1
End If
Next Cell

Range("A2").Value = vResult
End Sub

Though I had three cells with a colored formatting in the range, I get
the value of the cell A2 as zero instead of 3. Any idea, about this
mislead.

Thanks,
Thulasiram

 

[Thulasiram]

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]

Thulasiram

Are you sure the color index for those cells is "3"? I used code to set the
color index when I tested it. For example, in cells G1:I1 I entered 200,
100 and 340. Then I selected (Highlighted) cells "G1:I1". Then to ensure
I was using a color index of 3, I added the following code to the top of the
code I provided.

Selection.Interior.ColorIndex = 3

I steped through the code using F8. It counted the 3 cells that were
colored and added the values of each, totalling 640. It seems to worked on
my side.

Regards,
Paul

 

[Thulasiram]

Paul,

You spotted the problem correct.. Awesome.. I was using a colorindex 6
i.e. yellow. I was incorrect in my part.

Thanks for spotting that out. Now the code works perfect.. Great.

Regards,
Thulasiram