Counting Colored Cells

CheekyMLG · Jul 25, 2011

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 · Jul 25, 2011

CheekyMLG,

If the cells are NOT colored by conditional formatting, try:

Sample worksheet:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

1

100.00

*

1.00

101.00

*

Search Color

2

*

2

*

3

*

Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below Function(s) code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. Enter the Functions per the instruction(s) within the Functions.

Rich (BB code):

Option Explicit
Function SumByColor(CellColor As Range, SumRange As Range)
' SumByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 07/25/2011 by hiker95
' =SumByColor(J1,A1:G1)
' =SumByColor(cell_address_where_a_specific_color_is, range_to_Sum)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + myCell.Value
  End If
Next myCell
SumByColor = myTotal
End Function


Function CountByColor(CellColor As Range, SumRange As Range)
' CountByColor Function
' VBA and Macros for Microsoft Excel by Bill Jelen Mr.Excel, Page 84
' Modified 07/25/2011 by hiker95
' =CountByColor(J1,A1:G1)
' =CountByColor(cell_address_where_a_specific_color_is, range_to_Count)
Dim myCell As Range
Dim iCol As Integer
Dim myTotal
iCol = CellColor.Interior.ColorIndex
For Each myCell In SumRange
  If myCell.Interior.ColorIndex = iCol Then
    myTotal = myTotal + 1
  End If
Next myCell
CountByColor = myTotal
End Function

Enter the Functions per the instruction(s) within the Functions.

hiker95 · Jul 25, 2011

CheekyMLG,

If you are having a problem with the above, then can we have a screenshot of your worksheet?

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

CheekyMLG · Jul 25, 2011

Under M10 it should say there are 6 blue cells and under the Olive Green it should say there are 15 cells.

I appreciate your quick response.

I'm using Excel 2007

Also how do I set the macro to work in my on my first sheet?

Thank you very much for the help!

CheekyMLG · Jul 25, 2011

Looks Like I will be using the CountByColor function, It won't let me run it though? I'm not sure as to why, my macros are enabled, I get #NAME? also when I go to run the function it doesn't show up.

This message keeps showing up.......

Don't worry about the red section already figured out how I will do that.

CheekyMLG · Jul 25, 2011

Now this is what I am getting....

hiker95 · Jul 25, 2011

CheekyMLG,

You are posting a picture(s). This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

CheekyMLG · Jul 25, 2011

Here is the workbook.
It's on Sheet 3.

http://www.4shared.com/document/scONdHtg/Example_Book_4.html

Thanks for enlightening me on how to use these forums I appreciate it.

hiker95 · Jul 25, 2011

CheekyMLG,

Your link is not allowing me to download book 4.

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

CheekyMLG · Jul 25, 2011

hiker95 said:
CheekyMLG,

Your link is not allowing me to download book 4.

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net, and provide us with a link to your workbook.

Here is the link on BoxNet

http://www.box.net/shared/xs7bagg2ue1h95ghvjlg

Counting Colored Cells

CheekyMLG

New Member

Excel Facts

hiker95

Well-known Member

hiker95

Well-known Member

CheekyMLG

New Member

CheekyMLG

New Member

CheekyMLG

New Member

hiker95

Well-known Member

CheekyMLG

New Member

hiker95

Well-known Member

CheekyMLG

New Member

Similar threads

Forum statistics

Share this page

Counting Colored Cells

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock