counting by color

C

cc

I have followed instruvtions to count by color but when I insert the function
an error message comes up "takes no arguments'
 
C

cc

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

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

End Function
 
O

OssieMac

The function works.

For background (or interior color) red which is colorindex 3, you need to
enter the formula as:-
=SumByColor(A1:A14,3,FALSE)

The FALSE is optional when summing by background. You could use:-
=SumByColor(A1:A14,3)


For font color yellow which is colorindex 6, the formula is a follows:-
=SumByColor(A1:A14,6,TRUE). (TRUE is not optional here because that is what
tells the system that it is the font color and not the background color to
look for.
 
J

JE McGimpsey

One minor caveat -

This is really "SumByColorIndex", not SumByColor.

ColorIndex 3 in the default palette is red, and ColorIndex 6 in the
default palette is yellow

However, the color assignments may not hold true if the Color Palette
has been modified (e.g., manually, or by applying a different theme).

If one's users never change themes or color palette settings, that
shouldn't be a problem.
 
C

cc

Thanks for your assistance I'm a beginner at this and still cant seem to get
it. I have inserted the following as I'm using yellow

=SUMBYCOLOR(DD4:DD19,6,TRUE)
 
J

JE McGimpsey

That works for me for font color (after recalculating - just changing
the font color doesn't cause a recalc). You did intend font color (i.e.,
TRUE in the 3rd argument), right?

However, there are two "yellows" in the default Color Palette - the pale
one is ColorIndex 36.
 
C

cc

I've got it, thanks for your help guys.


JE McGimpsey said:
That works for me for font color (after recalculating - just changing
the font color doesn't cause a recalc). You did intend font color (i.e.,
TRUE in the 3rd argument), right?

However, there are two "yellows" in the default Color Palette - the pale
one is ColorIndex 36.
Click to expand...
 
H

Harald Staff

I've found it more user friendly to use "same color as this cell" instead of
hardcoding an obscure indexnumber, like

Function SumByColor(InRange As Range, SameColorAs As Range, _
Optional OfText As Boolean = False) As Double

Dim WhatColorIndex As Integer

If OfText = True then
WhatColorIndex = SameColorAs(1).Font.ColorIndex
Else
WhatColorIndex = SameColorAs(1).Interior.ColorIndex
....

(Haven't done this yet with 2007 though, so I'm not sure how it deals with
all variations of themes, pallettes and all that stuff, or if there's
another better approach.)

Best wishes Harald
 
Top