Hey all. I am currently working on a project, and what I need to do, Is sum
the values in a colomn where the fill color is red (or interior.color = 6). I
am not accustomed to using formulas in a macro, but since this will be a
variable range, I need to use a macro, How would I go about doing this?

How about this:

Dim r As Range
Dim iCount as Long

iCount = 0
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then iCount = iCount + 1
Next

MsgBox iCount

Using your range for the A1:B100 entry.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26894

This keeps returning the value of 0. Yes I have several of my lines red. But
correct me if I am wrong, but this would just sum the amount of cells that
are red, not the values in them correct?

Also I use a variable range

"kkknie" wrote:


How about this:

Dim r As Range
Dim iCount as Long

iCount = 0
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then iCount = iCount + 1
Next

MsgBox iCount

Using your range for the A1:B100 entry.

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...fo&userid=7543
View this thread: http://www.excelforum.com/showthread...hreadid=268949

Misread your post. The change would be:

Dim rCount as Double
Dim r as Range

rCount = 0

For Each r In Range("YourRangeName")
If r.Interior.ColorIndex = 6 Then rCount = rCount + r.Value
Next

Msgbox rCount

Are you sure the interior.colorindex of the cells is 6? One way t
check would be to locate one of the cells and run this code on i
(assuming A1 is red).

Msgbox Sheets("YourSheetName").Range("A1").Interior.Color Index

Another issue is conditional formatting. If the colors were change
that way, you cannot check for interior.colorindex. I've seen som
good explanations on that and could dredge them up if that is you
problem.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26894

It counts the number of cells that are red. If you want to sum them then

Dim r as Range, dblSum as Double
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then
if isnumeric(r) then
dblSum = dlbsum + r
end if
Next

However, since you are getting 0 for the count, I suspect your red color is
being produced by conditional formatting. Unfortunately, there isn't an
easy way to check what color is being produced by conditional formatting
(colorindex won't tell you that). In that case, you would need to check the
condition used by the conditional formatting. What is the condition for
summing? (if that is the case).


--
Regards,
Tom Ogilvy



"havocdragon" wrote in message
...
This keeps returning the value of 0. Yes I have several of my lines red.
But
correct me if I am wrong, but this would just sum the amount of cells that
are red, not the values in them correct?

Also I use a variable range

"kkknie" wrote:


How about this:

Dim r As Range
Dim iCount as Long

iCount = 0
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then iCount = iCount + 1
Next

MsgBox iCount

Using your range for the A1:B100 entry.

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile:
http://www.excelforum.com/member.php...fo&userid=7543
View this thread:
http://www.excelforum.com/showthread...hreadid=268949

typo:
Dim r as Range, dblSum as Double
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then
if isnumeric(r) then
dblSum = dblsum + r
end if
Next

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
It counts the number of cells that are red. If you want to sum them then

Dim r as Range, dblSum as Double
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then
if isnumeric(r) then
dblSum = dlbsum + r
end if
Next

However, since you are getting 0 for the count, I suspect your red color
is
being produced by conditional formatting. Unfortunately, there isn't an
easy way to check what color is being produced by conditional formatting
(colorindex won't tell you that). In that case, you would need to check
the
condition used by the conditional formatting. What is the condition for
summing? (if that is the case).


--
Regards,
Tom Ogilvy



"havocdragon" wrote in message
...
This keeps returning the value of 0. Yes I have several of my lines red.
But
correct me if I am wrong, but this would just sum the amount of cells
that
are red, not the values in them correct?

Also I use a variable range

"kkknie" wrote:


How about this:

Dim r As Range
Dim iCount as Long

iCount = 0
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then iCount = iCount + 1
Next

MsgBox iCount

Using your range for the A1:B100 entry.

K


--
kkknie

------------------------------------------------------------------------
kkknie's Profile:
http://www.excelforum.com/member.php...fo&userid=7543
View this thread:
http://www.excelforum.com/showthread...hreadid=268949

Actually I tried that on a blank sheet, and still not working, do I need an
add in ? Also I am working on excel 2000, so maybe there is something in that?


"Tom Ogilvy" wrote:

It counts the number of cells that are red. If you want to sum them then

Dim r as Range, dblSum as Double
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then
if isnumeric(r) then
dblSum = dlbsum + r
end if
Next

However, since you are getting 0 for the count, I suspect your red color is
being produced by conditional formatting. Unfortunately, there isn't an
easy way to check what color is being produced by conditional formatting
(colorindex won't tell you that). In that case, you would need to check the
condition used by the conditional formatting. What is the condition for
summing? (if that is the case).


--
Regards,
Tom Ogilvy



"havocdragon" wrote in message
...
This keeps returning the value of 0. Yes I have several of my lines red.
But
correct me if I am wrong, but this would just sum the amount of cells that
are red, not the values in them correct?

Also I use a variable range

"kkknie" wrote:


How about this:

Dim r As Range
Dim iCount as Long

iCount = 0
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then iCount = iCount + 1
Next

MsgBox iCount

Using your range for the A1:B100 entry.

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile:
http://www.excelforum.com/member.php...fo&userid=7543
View this thread:
http://www.excelforum.com/showthread...hreadid=268949

I would expect it to sum up to zero on a blank worksheet. Hardly the acid
test.

Before workbooks are addins, they are workbooks and the code has to work
before you make it an addin.

nothing in the code would have problems in xl2000.

if you want to be able to use this as a function in a worksheet cell, then

red (or interior.color = 6).

One problem may be that the colorindex for red is 3, not 6.

This worked for me:

Sub ABC_Sum()
Dim r As Range, dblSum As Double
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 3 Then
If IsNumeric(r) Then
dblSum = dblSum + r
End If
End If
Next
msgbox dblSum
End Sub

--
Regards,
Tom Ogilvy

"havocdragon" wrote in message
...
Actually I tried that on a blank sheet, and still not working, do I need
an
add in ? Also I am working on excel 2000, so maybe there is something in
that?


"Tom Ogilvy" wrote:

It counts the number of cells that are red. If you want to sum them
then

Dim r as Range, dblSum as Double
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then
if isnumeric(r) then
dblSum = dlbsum + r
end if
Next

However, since you are getting 0 for the count, I suspect your red color
is
being produced by conditional formatting. Unfortunately, there isn't an
easy way to check what color is being produced by conditional formatting
(colorindex won't tell you that). In that case, you would need to check
the
condition used by the conditional formatting. What is the condition for
summing? (if that is the case).


--
Regards,
Tom Ogilvy



"havocdragon" wrote in message
...
This keeps returning the value of 0. Yes I have several of my lines
red.
But
correct me if I am wrong, but this would just sum the amount of cells
that
are red, not the values in them correct?

Also I use a variable range

"kkknie" wrote:


How about this:

Dim r As Range
Dim iCount as Long

iCount = 0
For Each r In Range("A1:B100")
If r.Interior.ColorIndex = 6 Then iCount = iCount + 1
Next

MsgBox iCount

Using your range for the A1:B100 entry.

K


--
kkknie

------------------------------------------------------------------------
kkknie's Profile:
http://www.excelforum.com/member.php...fo&userid=7543
View this thread:
http://www.excelforum.com/showthread...hreadid=268949

Dude I am such a dumbass, my only excuse is its been such a long and eventful
week lol.

I changed the macro to read colorindex = 3 and it works wonderfully =)

The only thing I would like to know is, how to I get the rCount put
somewhere on the sheet, I have a few areas I would like to put it, but I
cannot find the correct procedure to paste it. thanks again guys, great stuff
=)

"kkknie" wrote:


Misread your post. The change would be:

Dim rCount as Double
Dim r as Range

rCount = 0

For Each r In Range("YourRangeName")
If r.Interior.ColorIndex = 6 Then rCount = rCount + r.Value
Next

Msgbox rCount

Are you sure the interior.colorindex of the cells is 6? One way to
check would be to locate one of the cells and run this code on it
(assuming A1 is red).

Msgbox Sheets("YourSheetName").Range("A1").Interior.Color Index

Another issue is conditional formatting. If the colors were changed
that way, you cannot check for interior.colorindex. I've seen some
good explanations on that and could dredge them up if that is your
problem.

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...fo&userid=7543
View this thread: http://www.excelforum.com/showthread...hreadid=268949

Not a problem:

Sheets("Sheet1").Range("A1").Value = rCount

will put it in A1 on Sheet1. Change these to fit your situation.

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...fo&userid=7543
View this thread: http://www.excelforum.com/showthread...hreadid=268949