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

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

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

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


Sjakkie wrote:
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

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

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

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


PCLIVE (RemoveThis) wrote:
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" wrote in message
ups.com...
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