Sum coloured cells

[Niv]

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.

 

[Pete_UK]

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

 

[stew]

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

 

[Bruno Campanini]

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.

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

 

[Bob Phillips]

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

--
---
HTH

Bob


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

 

[Niv]

Seehttp://www.xldynamic.com/source/xld.ColourCounter.htmlfor a working
solution

--
---
HTH

Bob

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








- 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.

 

[Niv]

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.- Hide quoted text -

- 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.