Counting Colored Cells

CheekyMLG

New Member
Joined
Jun 30, 2011
Messages
17
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
CheekyMLG,

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


Sample worksheet:


Excel Workbook
ABCDEFGHIJ
1100.00*****1.00101.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.
 
Upvote 0
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:
 
Upvote 0
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.

2zrlu8p.jpg


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!
 
Last edited:
Upvote 0
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.

nnrww8.jpg


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

Don't worry about the red section already figured out how I will do that.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top