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....
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.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    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.

      • sunilmishra86's avatar
        sunilmishra86
        Copper Contributor
        Mar 05, 2025

        will not work with conditional format works only with manual formatting

    • Damar9's avatar
      Damar9
      Copper Contributor
      Aug 21, 2024

      HansVogelaar 

      Apologies for resurrecting an old post. I am trying to do something similar and have found your solution very useful.

      I'm working on workbook which tracks student performance and colours the cells based on how they are doing against their targets.

       

      Your solution works perfectly for me to calculate the total number of each student by colour, however is it possible to combine with an IF or COUNTIF so that it only counts students whose set matches the one shown in the B column?

       

      i.e. H6 would count those students whose current grade in the CurrentYear7!Q5:Q301 range also match with the set shown in the B column here and is in a different column on the CurrentYear7 sheet.


       

      Thanks in advance,

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 21, 2024

        Damar9 

        Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

    • romancristiani's avatar
      romancristiani
      Copper Contributor
      Mar 05, 2023
      Hans,

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

Resources