Suming cells by colour

[siddhartharao]

Hi all,

how can you sum values, across worksheets, of various cells that are all the same colour? i.e. I would like the sum of all cells in the workbook that are filled in BLUE? Is this possible?

Thanks,

Sid

 

[schielrn]

Welcome to the Board!

This should do it for you, but you are a bit vague on what you want exactly and how you want the outcome displayed, so it is just a message box:

sub sumcolor()
for each ws in worksheets
for each c in ws.usedrange
if c.interior.colorindex = 5 then total = total + c.value
next c
next ws
msgbox total
end sub

You also didn't specify any columns or ranges so this goes through the used range of each sheet and may take some time to run depending on your sheets?

Hope that helps.

 

[katie1071]

Hi Sid,

I found this site to be helpful with colors. You could use it in conjunction with an IF statement.

http://www.cpearson.com/excel/SortByColor.aspx

Thanks
Katie

 

[Armando Montes]

Hi Sid, take a look here:
http://articles.excelyogi.com/sum-by-the-color-of-a-cell-excel/2009/02/18/