SOLVED

# Counting Cells by Color with conditional formatting

Copper Contributor

# Counting Cells by Color with conditional formatting

Each row represents a location. Each column is a metric that we track for each location. The metrics and goals are the same for each location. Each cell changes color based on conditional formatting. The cells of each column have the same conditions but the conditions change per column. The cells change constantly. New tabs are created monthly for each district. I need the total of the like colored cells to compute to the matching rows of the color coded columns below. Is this possible?

@Hans Vogelaar

8 Replies

# Re: Summing Cells by Color with conditional formatting

Do you really want to sum the values of (for example) red cells, or do you want to count them?

# Re: Summing Cells by Color with conditional formatting

Count them! Sorry

# Re: Summing Cells by Color with conditional formatting

I need to total number of Reds, Yellows and Greens in the respective color column below per location. Also Rows 4-7 that don't show in the photo above were deleted.

# Re: Summing Cells by Color with conditional formatting

best response confirmed by romancristiani (Copper Contributor)
Solution

# Re: Summing Cells by Color with conditional formatting

Here is the VBA code I came up with:

``````Function CountColor(rng As Range, colorcell As Range) As Long
Dim cell As Range
Dim clr As Long
clr = colorcell.Interior.Color
For Each cell In rng
If Evaluate("GetColor(" & cell.Address(External:=True) & ")") = clr Then
CountColor = CountColor + 1
End If
Next cell
End Function

Function GetColor(cell As Range) As Long
GetColor = cell.DisplayFormat.Interior.Color
End Function``````

The formula in B20 is

=IF(\$A20="", "", CountColor(\$B8:\$P8, B\$19))

This can be filled to the right to D20, ten down to row 25.

I have attached the workbook to a PM to you. You'll have to allow macros when you open it.

# Re: Summing Cells by Color with conditional formatting

Hans,

This is incredible. Everything works perfectly. I truly appreciate your help! Let me know if there is anything you need.

# Re: Summing Cells by Color with conditional formatting

Good to hear that it works.