VBA to updae count of colored cells based on value in another column/cell

[Prince27]

Hello Experts,

Looking for a VB code for the below scenario:

I have 2 sheets : 1 is Summary and another is Suppliers

In Suppliers sheet I have columns like Team Name & Due Date(with colors) as shown in the images.

I want count of colored cells based on a Particular Team Names into the summary sheet:

Summary sheet

	Color Amber	Color Yellow	Color Green
Team A	1		1
Team XYZ	1	1
Team DD	1	2	1

 

[Prince27]

Hi Experts,
Did any one got a chance to look into the above requirements

 

[kweaver]

This is a bit tricky (in my opinion). I created a Named Range which uses Get.Cell (only usable in Named Ranges).

Then found the colors and put them in the D column (fill down the simple formula). Then, compared them to the E column (which I manually colored and referenced the colors uniquely shown in column D.
Hope that at least gives you a start.

Book2
	C	D	E
1	Due Date		Count
2	8/15/2020	6	2
3	8/15/2020	6	3
4	9/1/2020	24	1
5	9/1/2020	24
6	9/1/2020	24
7	10/31/2020	40
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIF($D$2:$D$7,6)
E3	E3	=COUNTIF($D$2:$D$7,24)
E4	E4	=COUNTIF($D$2:$D$7,40)
D2:D7	D2	=MyColors

 

[kweaver]

Here's a function that'll count without using the D column.

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

F2 filled down; uses E column:

=GetColorCount($C$2:$C$7,E2)

 

[Prince27]

Thank for your quickresponse.

Actually i already have code (above) get color function.

But in my scenario i need get color function to be used in combination with another column ( count of color with multiple criteria on multiple columns ) text + color combination.

Appreciate any help on the same

 

[kweaver]

Doesn't GetColorCount do what you want? It counts the number of times a particular background color is found in a range.
Unless I missed it, you didn't specify other criteria.

Do you want to know, for example, now many times DD is in the Yellow?

 

[kweaver]

If the teams are in B and the dates with color backgrounds are in C, and color numbers are in D, maybe you want this:

=COUNTIFS($D$2:$D$7,D2,$B$2:$B$7,B2)

 

[Prince27]

Nopes, the above condition is for multiple criteria with text and text or text and number or number and number. (I mean more than 1 value or criteria)

Even my requirement is multiple criteria but combination of text and cell color

For example, in Column A there are 10 rows/cells named as Team A, Team B and so on and Column B has the 10 rows/cells filled with different colors (say red, blue , yellow)

i need a formula or VB code to get count of each color based on the different teams given in column A

 

[kweaver]

Book2
	B	C	D	E
1	Team	Due Date		Count
2	Team A	8/15/2020	6	1
3	Team B	8/15/2020	6	1
4	Team C	9/1/2020	24	2
5	Team D	9/1/2020	24	1
6	Team C	9/1/2020	24	2
7	Team A	10/31/2020	40	1
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=MyColors
E2:E7	E2	=COUNTIFS($D$2:$D$7,D2,$B$2:$B$7,B2)

I thought this looks at the D column (color) and also the B column (Team) and counts how many are, for example, TeamA and Yellow, etc...

 

[Prince27]

Okie got it Thank you !!