Forum Discussion

  • ernljohn 

    If the colors have been applied by conditional formatting, you should use the same criteria as those in the conditional formatting rule(s).

    If the colors have been applied directly, you'll need a custom VBA function. This will work in the desktop version of Excel for Windows and Mac, not in the browser nor on other platforms.

    And users will have to allow macros.

    Also, the formulas will not update their result automatically when you apply a different color to cells in the specified range. You'll have to do something that recalculates the sheet, for example pressing F9.

    Here is such a function:

     

    Function CountByColor(Range2Count As Range, ColorCell As Range) As Long
        Dim Cell As Range
        Application.Volatile
        For Each Cell In Range2Count
            If Cell.Interior.Color = ColorCell.Interior.Color Then
                CountByColor = CountByColor + 1
            End If
        Next Cell
    End Function

     

    Example:

    The formula in I2 is

    =CountByColor($B$2:$F$11, H2)

    • ernljohn's avatar
      ernljohn
      Copper Contributor

      HansVogelaar Thank you!  I was afraid there was no preprogramed function in Excell.  I will try your suggestion with the VBA defined function.  This will, of course, change the simple spreadsheet to one allowing macros.  But it will accomplish the task.

Resources