adding coloured cells together

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Link doesnt work!

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)

 

[Guest]

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)

 

[Bob Phillips]

It most certainly does!

--
HTH

Bob Phillips

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