Sum If question

[fbacchus]

Hi:

Can anyone send me the code required to check a range of cells (with numeric data) and if the data is "red" in color, sum them (only the reds) all. The workbook contains multiple worksheets and this can be the case for any of the worksheets.

Thanks in advance

frank

 

[JWARD]

look at this

http://www.vbaexpress.com/kb/getarticle.php?kb_id=89

 

[HalfAce]

Haven't checked out the VBAX link, but here's another one you may want to look into.

Just scroll down a bit till you find "Summing The Values Of Cells With A Specific Color"
http://www.cpearson.com/excel/colors.htm

Hope it helps,
Dan

 

[fbacchus]

[b]Sum If Question (Color numbers in Cell)[/b]

Thanks for the information. Dan, I checked the URL you gave me and it looks like what I an looking for under "Summing The Values Of Cells With A Specific Color".

I tried the code and it works well if "background color (fill)" is used. When I make the numbers "colored (e.g. RED)" it returns 0. How can I tell the code to look at the foreground (the color of the numbers).

I am a beginner with VBA so I and not clear how it works. I looks like "OfText indicates whether to return the ColorIndex of the Font (if True)" but I am not sure how to set "OfText".

thanks in advance

frank

 

[HalfAce]

Hi Frank, you're on the right track.
If you change the formula being used,
(example given is) =SUMBYCOLOR(A1:A10,3,FALSE)
to
=SUMBYCOLOR(A1:A10,3,TRUE)
it should sum up the values in the range A1:A10 with red font.
(The 3 in the formula refers to the color red. You would use 10 instead of 3 to sum up the values with green font, 6 for yellow font, etc.)

Hope it helps,
Dan

 

[HalfAce]

On looking at this again, maybe a little more complete reply might be more helpful. Here's a way to handle the whole thing.
Assumes your range is A1:A25, and the color to sum up is still red, and you're only concerned with the font color, not the background color and you want the summed values to appear in B26.

You can replace the function code with this:

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = True) As Double
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
OK = (Rng.Font.ColorIndex = 3)
    If OK And IsNumeric(Rng.Value) Then
        SumByColor = SumByColor + Rng.Value
    End If
Next Rng
End Function

Then in a standard module (the same one the function is in if you like) you can have this code.

Sub SumTheRedOnes()
[B26].Formula = "=SUMBYCOLOR(A1:A25,3,TRUE)"
End Sub

Now you can simply plant a button on the sheet to run the SumTheRedOnes code whenever there are any changes made to your range A1:A25.

And a more simple approach altogether might be to replace all of the above with this:

Sub SumRedOnly()
Dim Rng As Range
Set Rng = Range("A1:A25")
For Each cell In Rng
    If IsNumeric(cell.Value) And cell.Font.ColorIndex = 3 Then i = i + cell.Value
    Next cell
[B26] = i
End Sub

Does this help a little more?
Dan

 

[fbacchus]

Dan,

TRhnaks a great deal. I understand the code below and it look to be the simplest. One last question "What does [B26} represent ? Is that the designated cell where the results will show ?

thanks

frank

code:
--------------------------------------------------------------------------------
Sub SumRedOnly()
Dim Rng As Range
Set Rng = Range("A1:A25")
For Each cell In Rng
If IsNumeric(cell.Value) And cell.Font.ColorIndex = 3 Then i = i + cell.Value
Next cell
[B26] = i
End Sub

 

[HalfAce]

Exactly. [B26] (or otherwise written as) Range("B26") is where the sum of the red values will be put. You can replace that with any range you choose.

I agree this code can be more intuitive to read, but bear in mind that by using it you'll be sacrificing some speed so if you have a really huge range you're performing this on you'll likely notice the difference in the time it takes to execute. (But... as I see it, the slowest macro you can understand & make work is still faster than a quicker one you can't! )

Hope it helps,
Dan