total using colour?

Stormseed

Banned

Joined

Sep 18, 2006

Messages

3,274

• Apr 8, 2009

• #2

Look for the UDF Program CountColor() on this Chip Pearson's webpage:

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

 

Upvote 0

J

jackdiamond

Board Regular

Joined

Oct 10, 2007

Messages

220

• Apr 8, 2009

• #3

thanks, however, I am very slow at excel and can't make head nor tail of this?!

Could you help by any chance please?


I just need to count how many cells have blue in! (or any other colour)

Thanks

 

Upvote 0

Stormseed

Banned

Joined

Sep 18, 2006

Messages

3,274

• Apr 8, 2009

• #4

Could you please let me know the version of Excel that you are using on your computer ? It will hep me to guide you in the right kind of direction.

 

Upvote 0

J

jackdiamond

Board Regular

Joined

Oct 10, 2007

Messages

220

• Apr 8, 2009

• #5

excel 97 !

 

Upvote 0

jeffreybrown

Well-known Member

Joined

Jul 28, 2004

Messages

5,152

• Apr 8, 2009

• #6

Hi Stu,

You could try this...

Place the macro below in a standard module. Press Alt+F11 to enter the VBE > add a module and paste on the right side of the screen.

Excel Workbook
	I	J	K
1	*	*	*
2	*	*	*
3	*	*	*
4	*	*	2
Sheet2

Code:

Function COUNTCOLOR(rColor As Range, rCountRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim Result

Application.Volatile

iCol = rColor.Interior.ColorIndex

For Each rCell In rCountRange
If rCell.Interior.ColorIndex = iCol Then
Result = Result + 1
End If
Next rCell
'=COUNTCOLOR(K4,I1:J4)
'Where K4 houses the colour of choice (e.g. blue)
COUNTCOLOR = Result
End Function

 

Upvote 0

Stormseed

Banned

Joined

Sep 18, 2006

Messages

3,274

• Apr 8, 2009

• #7

Stu,
Jeff has helped through his post, post back incase you encounter problems

 

Upvote 0

J

jackdiamond

Board Regular

Joined

Oct 10, 2007

Messages

220

• Apr 8, 2009

• #8

ok, I press Alt+F11 to enter the VBE, I then insert module? Once the box comes up, I copy and paste the macro below.

What do I do now? How can i get that into my spreadsheet?

Apologies but I have never used VBE before

Any help appreciated!!!

Or if it is more helpful, could someone email me a version?!! stuart.longhurst@catalent.com

thank you very much
Stu

 

Upvote 0

jeffreybrown

Well-known Member

Joined

Jul 28, 2004

Messages

5,152

• Apr 8, 2009

• #9

Hi Stu,

That is good...you have pasted the Function in a standard module now all you have to do is enter the formula =COUNTCOLOR(K4,I1:J4) into your spreadsheet where K4 house the color you would like to count.

In the example I posted I1:J4 is the range being evaluated.

 

Upvote 0

J

jackdiamond

Board Regular

Joined

Oct 10, 2007

Messages

220

• Apr 8, 2009

• #10

Thanks for the email Jeff.

So I can do it myself in the future, once I have copied the section into VBE, how do I then get it so it is on the spreadsheet?

thanks
Stu

 

Upvote 0