Counting colors

[MartinLiss]

I have a worksheet that has over 20,000 rows, and in those rows conditional formatting is used to color the cells nine different colors depending on the cell's numerical values. For example,

• cells with a value from 1 to 9 are given a DisplayFormat.Interior.Color of 15189684
• cells with a value from 11 to 19 are given a DisplayFormat.Interior.Color of 6740479

(Number 10 get's its own color)

Currently I use VBA to count the total number of colored cells in each individual row and that takes about 20 seconds. Is there some formula I could use instead or some other method of speeding up the process?

[Zvoni]

Maybe here? https://trumpexcel.com/count-colored-cells-in-excel/
Approach 2 might work for you

[bahbahbah]

20 seconds is an awfully long time - can you post your code?

[Zvoni]

After researching this: The Problem is conditional Formatting.
You cannot use DisplayFormat in any UDF, only directly in VBA-Code

[MartinLiss]

Thanks Zvoni, but I believe the colors being counted in that article are cell.Interior colors whereas my colors are the result of conditional formatting rules and for those you need to refer to cell.DisplayFormat.Interior.Color

[Zvoni]

Thanks Zvoni, but I believe the colors being counted in that article are cell.Interior colors whereas my colors are the result of conditional formatting rules and for those you need to refer to cell.DisplayFormat.Interior.Color

Correct.
I fell for that trap before i found out about DisplayFormat.
Question: How do you "trigger" the vba-code now?
I've seen mention to use Worksheet_Change resp. Worksheet_Calculate-Event for stuff like that

EDIT: I've played around a bit
The "main" is just to fill 5 columns with 20,000 random numbers in the range 0 to 59
and i've cond. Formatting with 5 different colors except for the range 30 to 39, which gets no color
Column F displays correct number of "colored" columns.
Yes, it takes its time through the first run through, but any change afterwards is pretty much in realtime

Sub main()
Dim i As Long
Dim j As Long
    For i = 1 To 5
        For j = 2 To 20000
            Me.Cells(j, i) = Int(60 * Rnd)
        Next
    Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim CountColor As Long
    If Target.Column < 6 Then
        CountColor = 0
        For i = 1 To 5
            If Cells(Target.Row, i).DisplayFormat.Interior.Color <> 16777215 Then CountColor = CountColor + 1
        Next
        Me.Cells(Target.Row, 6) = CountColor
    End If
End Sub

[MartinLiss]

I apologize for wasting your time. I've just discovered that most of the 20 seconds is due to the time it takes to calculate formulas similar to the following (where "Z" varies from "Z" to "AI") in 10 columns.

=SUMPRODUCT(($C$4:$V$21498=Z2)*(SUBTOTAL(103,OFFSET(C4,ROW($C$4:$C$21498)-MIN(ROW($C$4:$C$21498)),0))))

Note that I can't switch to COUNTIF because I need to ignore rows that are filtered.