sum based on cell colour

[Hannah]

Hi,
just wondering how to go about summing a number of values in a column based on the background colour of the cell.

For example, say i have a list of numbers in column A starting at the cell A20, and a number of the values in this list are in cells that are a different colour, say red.

What i want to do is calculate the sum of only those values that are in red cells and insert that total in the first empty cell after the list of numbers.

I've tried a number of things but i'm way off the mark...can anyone get me back on track?

Thanks
H...

 

[The Idea Dude]

Hi Hannah,

I am not that great at this stuff, but I can think of one way to do it.

Firstly, why would the cells be red, is this a conditional format?

If so, your cell where the answer goes could be something like

=sumif( then the formula from the conditional format)

Any help?
This message was edited by The Idea Dude on 2002-09-11 19:33

 

[dangre]

Try the 4th tip on this web page, it deals with exactly what you're trying to accomplish....


http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm

 

[Hannah]

There's just one problem....I don't know much about functions but i'd already had a look at that site and decided that it wasn't for me....this was based mainly on my complete lack of knowledge of UDFs

So what i can gather is they're used like other stuff like SUM, AVG etc etc??? Is that right???

If that's the case i don't think that's what i'm after....I just wanted to do it through a macro and slot it into some other stuff i'm doing. If i use all that code i'd have to write more to get it doing what i want it to and i thought it was a bit more simple...

I know now i'm rambling....i just don't think i should use all that stuff if i don't understand it if you know what i mean....

is there a way i can do it in an everyday macro?

 

[PaddyD]

an alternative:

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