formulas with color

[lucas]

Does anyone know how I would create a formula that
designates and SUM of cells by the color of it's
background?

For example if A1 - A10 have numbers in them but, I only
want to add the cells that have a yellow background. What
would be my formula that I have to enter into A11 to get
that SUM.

Any help would be appreciated.

Thank you

 

[Peo Sjoblom]

Chip Pearson has code for that here

http://www.cpearson.com/excel/colors.htm

 

[David Hager]

See:

http://www.cpearson.com/excel/colors.htm

David Hager
Excel FMVP

 

[lucas]

I have consulted his site and I am still not doing
something correct because all I seem to get is #NAME?.

Here is the formula that I am using:
=SUMBYCOLOR(B5:B18,3,FALSE)

Does it matter which color in the pallet that I am using?
I am not totally understanding what the 3 does for me. Is
that the color from the pallet? How is the pallet
numbered?

 

[Chip Pearson]

Lucas,

If you are getting a #NAME error, then you are not putting the code in the
proper place. In the VBA editor, go to the Insert menu, choose Module, and
put the code in that module.

The '3' in the formula is the ColorIndex value for which you want to sum
(red, in this case). Change this value to the appropriate color index. The
color pallet consists of 56 entries, each of which is a Color value.
ColorIndex is the index or entry-number in to the pallet. So a ColorIndex of
3 is the third color value in the pallet.

 

[Tom Ogilvy]

Put the code in a general module, not the sheet module

in the VBE, look in help for colorindex.

The 3 is for red

David McRitchie can show you how the palette maps to the color index
numbers. You need to scroll down a ways at:

http://www.mvps.org/dmcritchie/excel/colors.htm

 

[Tom Ogilvy]

If you run this macro, with a blank sheet active, (put it in a general
module), it will show you the palette mapping to colorindex numbers as seen
in tools=>Options, color tab. the top 5 rows show the palette as shown on
the formatting toolbar fill icon and font color icons. Or you can look at
the numbers in the Varr1 section of SetPaletteArray

Sub ShowPalette()
varr = SetPaletteArray
Set rng = Cells(1, 1).Resize(7, 8)
rng.Value = varr
rng.HorizontalAlignment = xlCenter
For Each cell In rng
cell.Interior.ColorIndex = cell.Value
Next
Range("A:H").ColumnWidth = 3.29
Range("A1:H2,A7,H7,E6,E7,F7").Font.ColorIndex = 2
End Sub

Public Function SetPaletteArray()

varr1 = Evaluate(" { 1,53,52,51,49,11,55,56; " & _
"9,46,12,10,14,5,47,16;" & _
"3,45,43,50,42,41,13,48;" & _
"7,44,6,4,8,33,54,15;" & _
"38,40,36,35,34,37,39,2;" & _
"17,18,19,20,21,22,23,24;" & _
"25,26,27,28,29,30,31,32}")

SetPaletteArray = varr1
End Function

 

[Tom Ogilvy]

Red (3) is the 3rd color in the first column, but few of the other colors
follow that pattern.