Forum Discussion
ernljohn
Jun 22, 2024Copper Contributor
excell logical functions based on cell fill color
I would like to use the countif function with a criteria based on the cell's fill color.
HansVogelaar
Jun 22, 2024MVP
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
Jun 22, 2024Copper 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.