Counting Colored Cells

[CheekyMLG]

I need to take a certain range and depending on the color have a cell sum the number of cells that are the certain color. I could not find a solution with the functions. Do I have to write VB script? If so any ideas? If I don't need to

Ex. Cells contain color: Olive Green, Accent 3, Lighter 40%

Need a cell to add up how many cells are given this background

I'm new to this so any help would be much appreciated.

 

[hiker95]

CheekyMLG,

The reason the function is not working in cell I11, is because you have a rangename CountByColor assigned to cell I11.


To remove the rangename CountByColor:
Click in cell I11
Click on Insert, Name, Define…
In the Names in workbook: box
Click on COUNTBYCOLOR
And click on the Delete button

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		34235234	Number Pulled	345452	Draft Written	123213	Complete		Here is a small example I can't post the actual one because it's confidential. I need it to calculate the number of blue cells.So there are 6 blue cells so in M10 I would like it to tell me how many blue cells there are.In M11 I would like it to tell me how many OliveGreen Accent 3 lighter 40% cells there are etc.
2		234234		2342155	Complete	121
3		23423412	Draft Written	2341		123123	Draft Written
4		1232334		3435655	Draft Written	12323	Draft Written
5		45656		234323	Complete	345344
6		7676878	Number Pulled	234234		3434	Number Pulled
7		7878989	Complete	23412		54545	Complete
8		89090	Draft Written	2342	Number Pulled	6456						Blue	Olive Green
9		5675678		345345	Draft Written	3453	Draft Written
10		56734		34534		345345	Complete
11									1		Search Color
12
13
14			Will need It to cut the number in half because I only need it to count D, F, H. not all 6 columns.
15
16
17
18
19
20
21
22
23
24
25	Number Pulled
26	Draft Written
27	Complete
28
Sheet3

 

[hiker95]

CheekyMLG,

Plus, in the VBA Editor, you have a Module4 with just the following with no code between Sub and End Sub) (delete this code):

Sub COUNTBYCOLOR()

End Sub

 

[Kenneth Hobson]

After you fix the duplicate name in a Sub and the range name, try this routine.

Function CountByColor1(CellColor As Range, SumRange As Range) As Long
  ' CountByColor Function
  ' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
  ' =CountByColor(J1,A1:G1)
  ' =CountByColor(cell_address_where_a_specific_color_is, range_to_Count)
  Dim myCell As Range
  Dim iCol As Long
  Dim myTotal As Long
  iCol = CellColor.Interior.Color
  myTotal = 0
  For Each myCell In SumRange
    If myCell.Interior.Color = iCol Then
      myTotal = myTotal + 1
    End If
  Next myCell
  CountByColor1 = myTotal
End Function

 

[CheekyMLG]

CheekyMLG,

Plus, in the VBA Editor, you have a Module4 with just the following with no code between Sub and End Sub) (delete this code):

Sub COUNTBYCOLOR()

End Sub

could you post the workbook fixed via Boxnet so I can see what I did wrong?

I do what you say, but after insert, there is not name....

Thanks btw. I appreciate it more than you can understand

 

[hiker95]

CheekyMLG,

One last thing to delete.

In the VBA Editor, above the Modules you should see a list in Microsoft Excel Objects.

Double click on Sheet3(Sheet3), and in code section to the right (where the cursor is blinking) erase all this duplicate code.

 

[CheekyMLG]

CheekyMLG,

One last thing to delete.

In the VBA Editor, above the Modules you should see a list in Microsoft Excel Objects.

Double click on Sheet3(Sheet3), and in code section to the right (where the cursor is blinking) erase all this duplicate code.

Can you send me an updated one that you did. So I can see...

Also I keep doing what you tell me, but it's not working...Not sure why.

Also If you could extend the range to the whole colored box B1:G10


That would help massively.
Thanks man.

 

[hiker95]

CheekyMLG,


The functions that I posted earlier are not working in your workbook.


The below screenshot is using Kenneth Hobson's slightly different function, and it is working correctly.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		34235234	Number Pulled	345452	Draft Written	123213	Complete		Here is a small example I can't post the actual one because it's confidential. I need it to calculate the number of blue cells.So there are 6 blue cells so in M10 I would like it to tell me how many blue cells there are.In M11 I would like it to tell me how many OliveGreen Accent 3 lighter 40% cells there are etc.
2		234234		2342155	Complete	121
3		23423412	Draft Written	2341		123123	Draft Written
4		1232334		3435655	Draft Written	12323	Draft Written
5		45656		234323	Complete	345344
6		7676878	Number Pulled	234234		3434	Number Pulled
7		7878989	Complete	23412		54545	Complete
8		89090	Draft Written	2342	Number Pulled	6456						Blue	Olive Green
9		5675678		345345	Draft Written	3453	Draft Written
10		56734		34534		345345	Complete
11									1		Search Color
12
Sheet3

Copy Ken's function, and put it in an empty Module in the VBA Editor.

Then in cell I11 enter:
=CountByColor1(K11,B1:B2)


Thank you Ken.

 

[hiker95]

CheekyMLG, Kenneth Hobson,


In my archive file Count Sum By Color.xls, all three functions work correctly?????

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	100.00						1.00	101.00		Search Color
2								2
3								2	Kenneth Hobson
Sheet1

 

[Kenneth Hobson]

Here is the CountColors.xlsm that I put up for you at box.net. I just made a new file. Of course when I copied your data, I had to delete the named range CountColors.
CountColors.zip

 

[CheekyMLG]

CheekyMLG, Kenneth Hobson,


In my archive file Count Sum By Color.xls, all three functions work correctly?????

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	100.00						1.00	101.00		Search Color
2								2
3								2	Kenneth Hobson
Sheet1

It works!!!!!


Thank you guys so much!!!! I appreciate the help and the quickness of your responses.