Formula for counting cells with Fill Color

[dean_mcwhorter]

Is there away to count cells with 1 or more different fill colors in a column?

Thanks, Dean

 

[mikerickson]

Are the colors set by the user or by Conditional Formatting?

 

[dean_mcwhorter]

Not sure of the language but...

It's simply using the fill color formatting pallet in excel.

Thanks for quick reply.

 

[mikerickson]

That is filled by the user. A UDF could be used to count cells by their fill color.

I've found that a helper column is an easier way to mark rows than coloring cells.

This UDF will count the number of colored cells in a range.

Function CountOfColor(aRange As Range, ColorIndex As Double) As Double
    Dim i As Long
    Application.Volatile
    If ColorIndex < 1 Then ColorIndex = xlNone
    With aRange
        For i = 1 To .Cells.Count
            CountOfColor = CountOfColor - (.Item(i).Interior.ColorIndex = ColorIndex)
        Next i
    End With
End Function

 

[Simon Lloyd]

Mike hope you don't mind me stepping in here

Dean, Mikes code is for use in another macro or on the worksheet like this =countofcolor(A8:B12,3) where you have to stippulate the range and the colour you are counting, did you just want to count all cells with a coulour in them?

 

[dean_mcwhorter]

Thanks both you guys.....Simon yours seems more of my liking.

The other seems more in the access VB area. Mine is just simple counting
fill colors.

Great Site!.....and fast responses!

Thanks Again, Dean

 

[Simon Lloyd]

You misunderstand, the formula i showed you IS Mike's, thats how you use it on the worksheet, but you want to count all coloured cells, yes?, do the coloured cells have text in them?

 

[mikerickson]

Simon, feel free to help.

I'll again suggest that the plan for the spreadsheet be changed.
Colored highlights can be useful for conveying data to a human, but is not a good "data coding" technique for communicating to Excel.

Humans can easily understand that a highlighted a row means "Important".
Excel has a much easier time interpreting entries in a Level-of-Import (1, 2, 3) column than reading meaning into a color.

Debugging and maintaining a spreadsheet can get horrible if there is a "code" that Red=West Division, Blue=North, Yellow=East, etc. that needs to be remembered by users (and the file maintainer).

I strongly advise against a scheme where the color of a cell is data to be analyzed by formulas.

The UDF approach that I posted has a flaw, in that changing the fill color does not trigger the calculation of even a Volatile function.
A Change event won't work either, for the same reason.
(Another example that Excel is not built to handle color as data.)

 

[dean_mcwhorter]

Sorry Mike/Simon, 1st day on site and just found quick reply icon.

Yes i just want to count colored cells and they will have numeric values ion them.

 

[dean_mcwhorter]

Mike,

I agree, but what we have is a simple datasheet that the grader highlights areas that are out of spec. They simply highlight in yellow if out.