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" <(E-Mail Removed)> wrote in message
newsEC6EED9-8849-4DF4-A234-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> newsEC6EED9-8849-4DF4-A234-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> newsEC6EED9-8849-4DF4-A234-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> newsEC6EED9-8849-4DF4-A234-(E-Mail Removed)...
> >> > 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
> >> >>
> >> >>
> >