Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rating: Thread Rating: 3 votes, 5.00 average. Display Modes
Old 09-11-2014, 11:46 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Talking 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
File Type: jpg 01 Excel DemoVBAColorCount.jpg (64.8 KB, 501 views)
File Type: jpg 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.
Rx_ is offline   Reply With Quote
Old 09-12-2014, 12:05 AM   #2
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 539 Times in 521 Posts
Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light Brianwarnock is a glorious beacon of light
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
Brianwarnock is offline   Reply With Quote
The Following User Says Thank You to Brianwarnock For This Useful Post:
Rx_ (09-15-2014)
Reply

Tags
background color , count , excel , merlot , sum

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
count cells in range by background colour smiler44 Excel 3 12-14-2013 02:23 AM
opens an excel file and copies values from access textboxes to excel cells deanvilar Modules & VBA 3 05-27-2013 08:27 AM
Pls Help !!! Assigning values to Excel cells from access database jasmeetsingh89 Modules & VBA 7 08-03-2012 07:20 PM
Interpreting Excel cell color geoB Modules & VBA 3 11-26-2008 01:40 PM
open excel and color cells sam_01 Excel 1 01-21-2008 07:05 AM




All times are GMT -8. The time now is 07:49 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


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World