Home Forum Contact

 Access World Forums [SOLVED] Excel - Count cells or Sum cell's values by background Color
 User Name Remember Me? Password
 Register FAQ Members List Social Groups Top Posters Search Today's Posts

 Thread Tools Rating: Display Modes
09-11-2014, 11:46 AM   #1
Rx_
Nothing In Moderation

Join Date: Oct 2009
Posts: 2,803
Thanks: 636
Thanked 341 Times in 311 Posts
Excel - Count cells or Sum cell's values by background Color

I found this and will put it here so I can find it when I need it. If you think this is useful to share, please let me know. Otherwise, I can just put them away in my private reserve on my networked hard drive located in my private reserve wine cellar.
VBA code to count and/or sum the cells by background color:
Search words: VBA Count and or sum cells based on background color

Tools needed for this code:
Copy of MS Excel 97 or later.
A bottle of Merlot preferably northern Napa Valley.

1. Use ALT + F11 keys to open Visual Basic for Applications window while pouring wine.
2. Click Insert > Module then paste the following code in the Module Window.
Code:
```Public Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then   ' Note this can be set to true or false
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function```
3. Save the code and apply the following formula in a cell:
To Count the colored cells use:
=colorfunction(A,B:C,FALSE)
To Sum the colored cells use:
=colorfunction(A,B:C,TRUE)

A: is the cell with the particular background color you want to calculate the count and sum.

B:C: is the cell range where you want to calculate the count and sum.

4. Take the following screenshot for example, enter the formula =colorfunction(A1,A1:D7,FALSE) to count the yellow cells. And use the formula =colorfunction(A1,A1:D7,TRUE) to sum the yellow cells.
Note: A1 will pass the background color of the cell A1 into the first argument

The second argument, A1:D7 is the range of cells to be evaluated.
The two arguments will need to be changed as needed.

See attached images of demo.

The demo shows 5 Cells A11 through A15 with the colors used.
The formula pasted in the cell uses A1 as the default. Change it to A11.
Now the background color of A11 us used to count and sum the cells that have a background of yellow.

Remember, bottle of Napa Valley Merlot is considered a valid business deduction since it is a required tool in the instructions.
Attached Images
 01 Excel DemoVBAColorCount.jpg (64.8 KB, 505 views) 02 Excel DemoVBAColor.jpg (74.3 KB, 504 views)

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!

Last edited by Rx_; 09-15-2014 at 05:59 AM.

 09-12-2014, 12:05 AM #2 Brianwarnock Retired     Join Date: Jun 2003 Location: Merseyside England Posts: 12,701 Thanks: 39 Thanked 547 Times in 529 Posts Re: Excel - Count cells or Sum cell's values by background Color RX_ You need to repost after selecting Disable smilies in text option , it appears below the input form. Brian __________________ What is this life if, full of care, We have no time to stand and stare I do not have Access these days 2015
 The Following User Says Thank You to Brianwarnock For This Useful Post: Rx_ (09-15-2014)

 Tags background color , count , excel , merlot , sum

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post smiler44 Excel 3 12-14-2013 02:23 AM deanvilar Modules & VBA 3 05-27-2013 08:27 AM jasmeetsingh89 Modules & VBA 7 08-03-2012 07:20 PM geoB Modules & VBA 3 11-26-2008 01:40 PM sam_01 Excel 1 01-21-2008 07:05 AM

All times are GMT -8. The time now is 12:12 PM.

 Microsoft Access Help General Tables Queries Forms Reports Macros Modules & VBA Theory & Practice Access FAQs Code Repository Sample Databases Video Tutorials Featured Forum post Sponsored Links
 Contact Us - Home - Privacy Statement - Top