Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-12-2013, 01:16 PM   #1
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
count cells in range by background colour

I need to count the cells in a range that contain a colour. the range may vary but I think I'm able to get the range anyway. I want to use a macro and not a formula. I have found the following formula and vba code function but need help modifying this so it will work by code and not formula. Can you please help? I cant workout the VBA code equivalent of the formula

thank you
smiler44

this does not work
dim gr as string
gr = CountColor(A1:A7, a1)



the formula is:
= CountColor(A1:A99, A1)

The function code is:
Code:
 Function CountColor(Rng As Range, RngColor As Range) As Integer
   Dim Cll As Range
   Dim Clr As Long
  Clr = RngColor.Range("A1").Interior.Color
    For Each Cll In Rng
     If Cll.Interior.Color = Clr Then
           CountColor = CountColor + 1
       End If
       Next Cll
End Function


Last edited by smiler44; 12-12-2013 at 01:16 PM. Reason: spelling error
smiler44 is offline   Reply With Quote
Old 12-13-2013, 02:35 PM   #2
Brianwarnock
Retired
 
Brianwarnock's Avatar
 
Join Date: Jun 2003
Location: Merseyside England
Posts: 12,701
Thanks: 39
Thanked 547 Times in 529 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: count cells in range by background colour

Idon't understand what you are trying to do, do wish to run this function countcolor from a sub? and do what with the answer?

lookup the Run command in VBA help
I don't understand this line

Clr = RngColor.Range("A1").Interior.Color

why isn't it just
Clr = RngColor.Interior.Color

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
Old 12-13-2013, 03:10 PM   #3
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: count cells in range by background colour

Brian, hopefully there will be a spread sheet attached. it shows a staff list split into 2 teams, each team is in alphabetical order. I have 14 workbooks all with similar staff lists, here I have taken two staff lists and put them onto one sheet. I have to take the names and what they are doing from all 14 workbooks and put them in one new workbook with all the names in column A and the activity in column d. in column E for example I will put the name of the site where people work on the same row as the first person from each team. before copying the names and whjat they are doing I need to get the total number of people that are in each site and the number of people that are at work.
the number of people in any of the teams can vary week on week. the only constant is that in each workbook the first name and what they are doing will be in row 2. I can get the range for the whole staff list by code and so copying and pasting into the new workbook is easy. in the new workbook column A and D could have some 200 entires. starting at row 2 in the attached workbook that only has about 18 names I want to count the number of blue cells from row2 to row 10, this time I know its row 10 but next time it may be a different row. once I know this I can work out where the green background starts . because the number of people can vary each week I'm not sure that a formula would work.

once I have gathered this data I can put it into the new work book at the same time as I paste the names and what they are doing


hope this helps
smiler44
Attached Files
File Type: xls forums.xls (42.0 KB, 87 views)


Last edited by smiler44; 12-13-2013 at 03:21 PM.
smiler44 is offline   Reply With Quote
Old 12-14-2013, 02:23 AM   #4
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 637
Thanks: 13
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: count cells in range by background colour

This could me the macro that does the trick for counting the cells by colour.
"no" is a variable that in my working workbook I will need to work out, this I can do I sure.
the colour "index" again I will know in advance although I wount know the range or even if it exists. this needs to be played with a bit more to see if I can put a loop into the macro and make the colourindex a variable. this would allow me to run the one macro across 20 rows and count each consecutive row of the same colour and then when the colour changes. if there are two colours such as red and green i'll have one number for red and one for green. this may stop me having to have a macro to count each colour. as I say this needs to be played with and experimented with but its looking good


the cell references differ from the workbook I posted but I hope you can follow the code.

smiler44

Code:
 Sub Macro1()
 Dim colourCellCount As Integer
Dim no As Long
no = 13 ' this would be the whole range
colourCellCount = 0
 Range("H2").Select ' this is where to start counting from
For N = 1 To no ' no is dimmed as long and is the whole range
    If Selection.Interior.Color = 255 Then ' the 255 is the colour index of the red
   ' If Selection.Interior.ThemeColor = xlThemeColorAccent3 Then 'xlThemeColorAccent3 is the colour code for the light green in my workbook
        colourCellCount = colourCellCount + 1
    End If
    ActiveCell.Offset(1, 0).Select
Next N
 Range("h13").Select
ActiveCell.FormulaR1C1 = colourCellCount
 End Sub

smiler44 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Count/sum on background colour? jessboo Crystal Reports 2 01-16-2012 04:10 AM
background colour Suepoo Forms 6 02-10-2007 04:15 PM
[SOLVED] Background colour DJN General 2 08-11-2004 09:16 AM
Background Colour wort0987654321 General 5 12-16-2003 01:14 PM
Background Colour Iwra Forms 4 01-07-2003 06:14 AM




All times are GMT -8. The time now is 10:45 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 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World