adding coloured cells together
 

I have been forwarded a spreadsheet where the cells have been filled with
colour.
The colours represent certain activities.

Is there any way I can add together all the values in the red cells, and all
the values in the green cells?

regards

adding coloured cells together
 

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
I have been forwarded a spreadsheet where the cells have been filled with
colour.
The colours represent certain activities.

Is there any way I can add together all the values in the red cells, and
all
the values in the green cells?

regards

adding coloured cells together
 

Link doesnt work!

"Bob Phillips" wrote:

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
I have been forwarded a spreadsheet where the cells have been filled with
colour.
The colours represent certain activities.

Is there any way I can add together all the values in the red cells, and
all
the values in the green cells?

regards

adding coloured cells together
 

In a general module (insert=Module in the VBE), put in a function like:

Public Function countcoloredCells(rng as range, lcolor as Long)
Dim cnt as Long
Dim cell as Range
for each cell in rng
if cell.interior.colorIndex =lcolor then
cnt = cnt + 1
end if
Next
countcoloredcells = cnt
End Function

red would be 3
Green would be 4

although there are some shades of these colors that could have a differnt
colorindex.

Select a sample cell and run this

sub showcolorIndex()
msgbox "Color index is " & activecell.interior.colorindex
End sub

usage
=countcoloredcells(A1:A100,3)

--
Regards,
Tom Ogilvy


"TheRook" wrote:

Link doesnt work!

"Bob Phillips" wrote:

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
I have been forwarded a spreadsheet where the cells have been filled with
colour.
The colours represent certain activities.

Is there any way I can add together all the values in the red cells, and
all
the values in the green cells?

regards

adding coloured cells together
 

It most certainly does!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
Link doesnt work!

"Bob Phillips" wrote:

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TheRook" wrote in message
...
I have been forwarded a spreadsheet where the cells have been filled
with
colour.
The colours represent certain activities.

Is there any way I can add together all the values in the red cells,
and
all
the values in the green cells?

regards