count cells in range by background colour (1 Viewer)

smiler44 · Dec 12, 2013

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

Brianwarnock · Dec 13, 2013

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

smiler44 · Dec 13, 2013

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

smiler44 · Dec 14, 2013

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

count cells in range by background colour (1 Viewer)

smiler44

Registered User.

Brianwarnock

Retired

smiler44

Registered User.

Attachments

smiler44

Registered User.

Similar threads

Users who are viewing this thread