Adding Values According to Formatting

B

Brandt

I would like to create a formula to sum the values in a column but only
include those values which have the cell formatting of yellow. This way I
can fomat the cells yellow which I want included in the summation without
having to manually adjust the formula in the cell after I have formatted a
new cell in the column. Is this possible?

(This formula does not necessarily need to include the entire column but
maybe J1-J50 if that is easier.)

Thanks in advance for any help.
 
B

Brandt

Thanks,

It looks like this is exactly what I need. I have used macros enough that I
am familiar with them, but I am not so sure about functions. Where do I copy
the code to that is shown on the website? Do I put it in a module? Can I
put it in personal.xls? As of now I have copied the text into module 4 of
personal.xls (I did this as though I were creating a macro, but then copied
the function text) and it does not work in the file where I want to use it.
The code I copied in looks like this:

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

' You can call this function from a worksheet cell with a formula like
' =CELLCOLORINDEX(A1,FALSE)


End Function

Which is exactly from the website except I have included the 2 comment lines
at the end.
 
B

Brandt

I should have mentioned that the error I get when attempting to use this is
the #NAME? error.

Thanks
 
T

tjtjjtjt

Where it says interior.colorindex, you need the color index for the colr you
are using.
Easiest way to find it would be to record a macro while giving a cell the
background color you need. Then, Go to Tools | Macro | Macros. Find your
macro in the list. Click on it and choose edit.
Locate the number for the interior color index.
Now go to the code for the macro from the web site and replace the words
colorindex with the actual number.

tj
 
T

tjtjjtjt

Ack! Disregard the post where I said delete stuff.
Actually, don't erase the .colorindex
Add an = and the number after it.
It will look something like this:

CellColorIndex = InRange(1, 1).Interior.ColorIndex = 6

sorry, must be too late in the day for me.

tj
 
T

tjtjjtjt

Actually, I think you are trying to use the wrong Function from the web site.
Look at the Function beneath the heading:
Summing The Values Of Cells Based On The Color Of Other Cells

You are currently using the one above that heading.

Try http://www.cpearson.com/excel/differen.htm for some ideas on where to
store your macro.

tj
 
D

Dave Peterson

First, put the function in a general module--not behind a worksheet, not in a
class module, not in the ThisWorkbook module.

And if you put the function into your personal.xls workbook, you'll have to use
this type of syntax:

=personal.xls!cellcolorindex(a1, true)
 
Top