Mar 04 2023 05:57 PM - edited Mar 05 2023 08:34 AM
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?
Mar 05 2023 01:54 AM
Maybe this link will help you :).
Mar 05 2023 04:13 AM
Do you really want to sum the values of (for example) red cells, or do you want to count them?
Mar 05 2023 06:55 AM
Mar 05 2023 08:48 AM
SolutionHere 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.
Mar 05 2023 02:57 PM
Mar 05 2023 03:04 PM
Good to hear that it works.