Need a Macro that will sum Values in a Column that are red

[Guest]

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?

 

[kkknie]

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.

 

[Guest]

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]

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.ColorIndex

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.

 

[Tom Ogilvy]

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).

 

[Tom Ogilvy]

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

 

[Guest]

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]

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

 

[Guest]

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]

Not a problem:

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

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

K