Counting Cells by Color

[Slicemahn]

Hello MrExcel Nation!

I am setting up a planning worksheet to see where I am over/under utilized. The challenge I am having is counting cells by color. There are some activities that require 2 resources and the other cells that have text without any cell color (or white) require just one resource. Now easily I can do a COUNTA function to capture the cells with text but how can I add 2 resources for every gray cell without manually typing in a calculation?

Book1
	A	B	C	D	E	F
1	Facility	North	South	East	West	Central
2	Activity1	Tec		Tec		Tec
3	Activity2	Tec		Tec		Tec
4	Activity3
5	Activity4
6	Activity5
7	Activity6
8	Activity7				Tec
9	Activity8			Tec
10	Activity9	Tec
11	Activity10				Tec
12	Activity11				Tec
13	Activity12
14	Activity13
15	Activity14
16	Activity15
17	Activity16
18	Activity17
19	Activity18
20	Total Required	9	4	7	6	3
Sheet1

Thank you for your help! This is difficult for me.

 

[davesexcel]

There are a ton of threads on this.
I just wrote this up place a colored cell in J1 and the code will loop through the range and add up the cells that match the color.

Sub Button1_Click()
    Dim sh          As Worksheet, rng As Range, c As Range
    Dim clr         As String
    Set sh = ActiveSheet
    With sh
        clr = .Range("J1").Interior.Color
        Set rng = .Range("B1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For Each c In rng.Cells
            If c.Interior.Color = clr Then
                .Range("I1") = .Range("I1") + 1
            End If
        Next
    End With
End Sub

-

 

[Slicemahn]

Hi Davesexcel,
Thank for this solution! But I am interested in counting by location so the columns North, South, East, West and Central. So for North there are three "Tecs" and three gray boxes. Well the three gray boxes should count as two. Therefore the total required will be COUNTA(B2:B19) + Function(3 gray cells * 2) to give a total of 9. That is what I am looking to do. So can your code translate to a function that can add these two factors (text cells + gray colored cells)?

 

[Rick Rothstein]

A possible solution, put a number 2 in each of the gray cells and set the font color to the same color as the fill color... you can then use a simple formula to sum the column and add it to a COUNTA calculation for the column to get your totals. You could create a macro to do the filling with gray and entering the 2 and assign that to shortcut (or a button) to make filling the gray cells easier.

 

[Slicemahn]

A possible solution, put a number 2 in each of the gray cells and set the font color to the same color as the fill color... you can then use a simple formula to sum the column and add it to a COUNTA calculation for the column to get your totals. You could create a macro to do the filling with gray and entering the 2 and assign that to shortcut (or a button) to make filling the gray cells easier.

A good solution but I don’t have the time to go through 40 weeks of planning by 5 different locations. I was hopeful of a function that would compile the results quickly.

 

[Rick Rothstein]

Just so I understand your setup, does 40 weeks mean 40 sheets?

If so, are there any other sheets in the workbook besides those 40 sheets?

Also, if so, then are those 40 sheets all setup the same (5 columns each with the same number of activities?

 

[Slicemahn]

Unfortunately no. It is a workbook for every region with tabs for each week in a month. So each month tab you will see the locations for different number of activities, because each plant site operates on different projects.

 

[davesexcel]

The sample you provided does not match your explanation.
Column D should be 11, E should be 9, F should be 4

Here is a Function:

Function aa_sumPlusClr(rng As Range, colr As Range)
    Dim sh As Worksheet
    Dim c As Range
    Dim x As Long, t
    Set sh = ActiveSheet
    With sh
        For Each c In rng.Cells
            If c.Interior.Color = colr.Interior.Color Then
                x = x + 1
            End If
        Next
    End With
    t = Application.WorksheetFunction.CountIf(rng, "*")
    aa_sumPlusClr = t + x * 2
    
End Function

In B20 enter this formula and drag accross

=aa_sumPlusClr(B2:B19,$J$1)

J1 is the cell that has the color to find

 

[Slicemahn]

The sample you provided does not match your explanation.
Column D should be 11, E should be 9, F should be 4

Here is a Function:

Function aa_sumPlusClr(rng As Range, colr As Range)
    Dim sh As Worksheet
    Dim c As Range
    Dim x As Long, t
    Set sh = ActiveSheet
    With sh
        For Each c In rng.Cells
            If c.Interior.Color = colr.Interior.Color Then
                x = x + 1
            End If
        Next
    End With
    t = Application.WorksheetFunction.CountIf(rng, "*")
    aa_sumPlusClr = t + x * 2
   
End Function

In B20 enter this formula and drag accross

=aa_sumPlusClr(B2:B19,$J$1)

J1 is the cell that has the color to find

I love this! Thanks Dave! Works great. Thanks a million.