Sum coloured cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Niv - 06 Mar 2008 11:53 GMT

I have a small spreadsheet, say 52 rows X 7 columns (yes, it's a year)
Some cells are colour filled red, some blue etc., but are randomly
spread across the cells
I want a separate sum for all cells that have a red fill color, for
those with a blue fill colour, etct etc.

Can anyone help please?
I'm not familiar with VB, so I'm struggling somewhat.

TIA, Niv.

Reply to this Message

Pete_UK - 06 Mar 2008 13:09 GMT

Well, you will need to use VBA to do this as it is not a standard
feature of Excel, but Chip Pearson explains how to do it here:

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

Hope this helps.

Pete

> I have a small spreadsheet, say 52 rows X 7 columns (yes, it's a year)
> Some cells are colour filled red, some blue etc., but are randomly
[quoted text clipped - 6 lines]
>
> TIA, Niv.

Reply to this Message

stew - 06 Mar 2008 13:23 GMT

If you are not confident in programming, another solution, if you have
four or less colours on your spreadsheet, is to use conditional
formatting (from the Format menu).

Before starting this soltion, you would enter an R into all your cells
that you want to be Red, B for Blue, G for Green etc.

Use conditional formating to build a rule for your first cell, to
apply an appropiate colour (If Cell Value is Equal to R - Press format
button and select Red for the Patterns and Font). Press Add and repeat
for the other colours. Once built for a single cell you can use the
format painter to apply to other cells.

Once the spreadsheet is recoloured, you can use the formula

=COUNTIF(A:G,"R")

to tell you how many cells in columns A through to G are "R"ed. And
similar formula for other colours.

Programming would be better though!

Stewart

Reply to this Message

Bruno Campanini - 06 Mar 2008 14:11 GMT

>I have a small spreadsheet, say 52 rows X 7 columns (yes, it's a year)
> Some cells are colour filled red, some blue etc., but are randomly
[quoted text clipped - 6 lines]
>
> TIA, Niv.

Sub Button24_Click()
Dim i, k
For Each i In [Sheet4!B79:B92]
If i.Interior.Color = 5296274 Then
k = k + i
End If
Next
MsgBox k
End Sub

Replacing 5296274 (Green) with your color
and [Sheet4!B79:B92] with your Sheet and range.

Bruno

Reply to this Message

Bob Phillips - 06 Mar 2008 14:16 GMT

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

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have a small spreadsheet, say 52 rows X 7 columns (yes, it's a year)
> Some cells are colour filled red, some blue etc., but are randomly
[quoted text clipped - 6 lines]
>
> TIA, Niv.

Reply to this Message

Niv - 06 Mar 2008 16:36 GMT

> Seehttp://www.xldynamic.com/source/xld.ColourCounter.htmlfor a working
> solution
[quoted text clipped - 19 lines]
>
> - Show quoted text -

OK, I've pasted the functions from bobs but I just get #NAME? when I
put =colorindex(A1) in a cell and color A1 red, for example

Niv.

Reply to this Message

Niv - 07 Mar 2008 08:00 GMT

> > Seehttp://www.xldynamic.com/source/xld.ColourCounter.htmlfora working
> > solution
[quoted text clipped - 30 lines]
>
> - Show quoted text -

It's working now, not sure what I did wrong originally.
Thanks for the help, especially Bob, I pasted in your VB functions.

Niv.

Reply to this Message

Rate this thread: