Need to sum "last 5" red cells
 

Look at http://www.xldynamic.com/source/xld.ColourCounter.html and use
something like

=SUM(TRANSPOSE(OFFSET(A6,0,LARGE(IF(ColorIndex(A6: L6)=3,COLUMN(A6:L6)),{1,2,
3,4,5})-1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

If the data is columnar, use

=SUM(TRANSPOSE(OFFSET(A1,LARGE(IF(ColorIndex(A1:A1 00)=3,ROW(A1:A100)),{1,2,3
,4,5})-1,0)))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mike" wrote in message
oups.com...
I'm playing with some color vba from cpearson.com but can't seem to
automate the process of summing the last x number of y colored cells.
I'm having to change ranges every time I copy the formula over.

Using the info on this page, http://www.cpearson.com/excel/colors.htm,
or their own imagination, can anyone come up with a script for the
above?

My ultimate goal is to keep a running average of red cells, but I can't
do that unless I know how many I have and where to tell it to start
summing. If I can just say "sum the last 5 red cells in row A:A" then I
won't have to keep adjusting formulas.

Any comments appreciated.

Need to sum "last 5" red cells
 

I'm playing with some color vba from cpearson.com but can't seem to
automate the process of summing the last x number of y colored cells.
I'm having to change ranges every time I copy the formula over.

Using the info on this page, http://www.cpearson.com/excel/colors.htm,
or their own imagination, can anyone come up with a script for the
above?

My ultimate goal is to keep a running average of red cells, but I can't
do that unless I know how many I have and where to tell it to start
summing. If I can just say "sum the last 5 red cells in row A:A" then I
won't have to keep adjusting formulas.

Any comments appreciated.

Need to sum "last 5" red cells
 

Hi Mike,

Try this.

Sub SumColorCountRedLastFive()
Dim Red3 As Integer
Dim Count As Integer
Dim i As Integer

Range("A1000").End(xlUp).Select
i = Range("A100").End(xlUp).Row
Count = 0

For i = 1 To i - 1
If ActiveCell.Interior.ColorIndex = 3 Then
Count = Count + 1
Red3 = Red3 + ActiveCell.Value
ActiveCell.Offset(-1, 0).Select
If Count = 5 Then
Exit For
End If

ElseIf ActiveCell.Interior.ColorIndex < 3 Then
ActiveCell.Offset(-1, 0).Select
End If
Next

MsgBox "The last five Red cells " _
& vbCrLf & "in column A add up to" _
& vbCrLf _
& vbCrLf & " " & Red3

End Sub

HTH
Regards,
Howard

"mike" wrote in message
oups.com...
I'm playing with some color vba from cpearson.com but can't seem to
automate the process of summing the last x number of y colored cells.
I'm having to change ranges every time I copy the formula over.

Using the info on this page, http://www.cpearson.com/excel/colors.htm,
or their own imagination, can anyone come up with a script for the
above?

My ultimate goal is to keep a running average of red cells, but I can't
do that unless I know how many I have and where to tell it to start
summing. If I can just say "sum the last 5 red cells in row A:A" then I
won't have to keep adjusting formulas.

Any comments appreciated.

Need to sum "last 5" red cells
 

I can't get either to work as I need. Howard, yours works but I need to
get the last 5 in the row, not column and display in a cell as opposed
to msgbox. I can't figure out how to convert it.

Bob, I just can't get that to work. Wonder if my Excel is recognizing
the ColorIndex funtion? Excel 2000... All I get is the #NAME? result.
ColorIndex isn't in the help file anywhere.

Need to sum "last 5" red cells
 

You have to copy the ColorIndex function off of that web page into your
workbook.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"mike" wrote in message
oups.com...
I can't get either to work as I need. Howard, yours works but I need to
get the last 5 in the row, not column and display in a cell as opposed
to msgbox. I can't figure out how to convert it.

Bob, I just can't get that to work. Wonder if my Excel is recognizing
the ColorIndex funtion? Excel 2000... All I get is the #NAME? result.
ColorIndex isn't in the help file anywhere.

Need to sum "last 5" red cells
 

http://www.cpearson.com/excel/whatsnew.htm

on the Chip Pearson web page you can find it

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"unknown" escreveu:

Need to sum "last 5" red cells
 

Hi Mike,

Try this. Change the 1 in "Range("AI1").End(xlToLeft).Select" to match the
row you are using. Returns the value to cell F10.

Sub SumRedLastFiveRow()
Dim Red3 As Integer
Dim Count As Integer
Dim i As Integer

Range("AI1").End(xlToLeft).Select

i = ActiveCell.Column

Count = 0

For i = 1 To i - 1
If ActiveCell.Interior.ColorIndex = 3 Then
Count = Count + 1
Red3 = Red3 + ActiveCell.Value
ActiveCell.Offset(0, -1).Select
If Count = 5 Then
Exit For
End If

ElseIf ActiveCell.Interior.ColorIndex < 3 Then
ActiveCell.Offset(0, -1).Select
End If
Next

'MsgBox "The last five Red cells " _
'& vbCrLf & " in row 1 add up to" _
'& vbCrLf _
'& vbCrLf & " " & Red3

Range("F10").Value = Red3
End Sub

HTH
Regards,
Howard

"mike" wrote in message
oups.com...
I'm playing with some color vba from cpearson.com but can't seem to
automate the process of summing the last x number of y colored cells.
I'm having to change ranges every time I copy the formula over.

Using the info on this page, http://www.cpearson.com/excel/colors.htm,
or their own imagination, can anyone come up with a script for the
above?

My ultimate goal is to keep a running average of red cells, but I can't
do that unless I know how many I have and where to tell it to start
summing. If I can just say "sum the last 5 red cells in row A:A" then I
won't have to keep adjusting formulas.

Any comments appreciated.

Need to sum "last 5" red cells
 

That one works differently, won't fit in with the formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Marcelo" wrote in message
...
http://www.cpearson.com/excel/whatsnew.htm

on the Chip Pearson web page you can find it

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"unknown" escreveu: