Forum Discussion

romancristiani's avatar
romancristiani
Copper Contributor
Mar 05, 2023
Solved

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?

 

 

HansVogelaar

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Mar 05, 2023

    romancristiani 

    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.

Resources