formulas with color

  • Thread starter Thread starter lucas
  • Start date Start date
L

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
 
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?
 
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.
 
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
 
Red (3) is the 3rd color in the first column, but few of the other colors
follow that pattern.
 
Back
Top