counting by color

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

OssieMac
Tue Jul 22 22:09:02 CDT 2008

Post an example of the formula you have attempted to create.

--
Regards,

OssieMac


"cc" wrote:

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

cc
Tue Jul 22 22:29:04 CDT 2008

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




"OssieMac" wrote:

> Post an example of the formula you have attempted to create.
>
> --
> Regards,
>
> OssieMac
>
>
> "cc" wrote:
>
> > I have followed instruvtions to count by color but when I insert the function
> > an error message comes up "takes no arguments'

OssieMac
Tue Jul 22 23:16:00 CDT 2008

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.


--
Regards,

OssieMac


"cc" wrote:

> 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
>
>
>
>
> "OssieMac" wrote:
>
> > Post an example of the formula you have attempted to create.
> >
> > --
> > Regards,
> >
> > OssieMac
> >
> >
> > "cc" wrote:
> >
> > > I have followed instruvtions to count by color but when I insert the function
> > > an error message comes up "takes no arguments'

JE
Tue Jul 22 23:39:44 CDT 2008

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.



In article <59669047-1B33-4A1A-B9B2-CB9CCFEAA445@microsoft.com>,
OssieMac <OssieMac@discussions.microsoft.com> wrote:

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

cc
Wed Jul 23 00:11:00 CDT 2008

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)



"JE McGimpsey" wrote:

> 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.
>
>
>
> In article <59669047-1B33-4A1A-B9B2-CB9CCFEAA445@microsoft.com>,
> OssieMac <OssieMac@discussions.microsoft.com> wrote:
>
> > 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.
> >
>

JE
Wed Jul 23 00:19:12 CDT 2008

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.


In article <B7C376E9-2F01-499D-9429-C5FD517FF676@microsoft.com>,
cc <cc@discussions.microsoft.com> wrote:

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

cc
Wed Jul 23 01:19:01 CDT 2008

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


"JE McGimpsey" wrote:

> 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.
>
>
> In article <B7C376E9-2F01-499D-9429-C5FD517FF676@microsoft.com>,
> cc <cc@discussions.microsoft.com> wrote:
>
> > 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)
>

Harald
Wed Jul 23 05:41:28 CDT 2008

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


"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-B3D862.22394422072008@news.microsoft.com...
> 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.
>
>
>
> In article <59669047-1B33-4A1A-B9B2-CB9CCFEAA445@microsoft.com>,
> OssieMac <OssieMac@discussions.microsoft.com> wrote:
>
>> 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.
>>