Count colored cells by conditional formatting

[YasserKhalil]

Hello everyone
I have a UDF that is supposed to count a range of conditional formatting and there is a weird behaviour
The udf works fine in the 'Test' macro but as for the worksheet function usage it gives a VALUE error ( the yellow cell) .. any idea how to fix that?

[HansV]

From the help for DisplayFormat:

Note that the DisplayFormat property does not work in user defined functions. For example, in a worksheet function that returns the interior color of a cell, you use a line similar to: Range(n).DisplayFormat.Interior.ColorIndex. When the worksheet function executes, it returns a #VALUE! error.

So use the macro, and avoid using the UDF.

[YasserKhalil]

So there is no way to count the colored cells by conditional formatting using a UDF?

[HansV]

No easy way. You'd have to check which (if any) of each cell's conditional formatting rules applies; this is very complicated with all the possibilities conditional formatting offers nowadays. Then check the corresponding formatting to decide whether the cell should be included or not.

[YasserKhalil]

Thanks a lot for great help Mr. Hans
I think using worksheet_SeelctionChange event would solve the problem in that case
Regards

[YasserKhalil]

Old thread but I found a solution (for those that may see that thread)

Code: Select all

Function ColorEA(rng As Range, rc As Range, Optional f As Byte = 3, Optional v As Boolean = 1) As Single
'Programming By Eslam Abdullah
'=ColorEA($E$17:$F$20,C21,,0) >> Count
'=ColorEA($E$17:$F$20,C21,,1) >> SUM
'-------------------------------------
    Dim r As Range, c As Range, result!, check$, num&, ii&, i&

    Application.Volatile

    If f = 1 Then
1:
        For Each c In rng
            If c.Interior.Color = rc.Interior.Color Then result = result + IIf(v, c.Value, 1)
        Next c
        GoTo 3
    ElseIf f = 2 Then
2:
        For i = 1 To rng.FormatConditions.Count
            If rng.FormatConditions(i).Interior.Color = rc.Interior.Color Then
                Set r = Range(rng.FormatConditions(i).AppliesTo.Address)
                ii = (rng.Row - r.Row) * r.Columns.Count + (rng.Column - r.Column)
                For Each c In rng
                    If c.FormatConditions.Count Then
                        num = Application.Min(c.FormatConditions.Count, i)
                        ii = ii + 1
                        check = IIf(rng.FormatConditions(i).Type = 1, rng.Cells(1).Address(0, 0) & rng.FormatConditions(i).Formula1, rng.FormatConditions(i).Formula1)
                        check = Application.ConvertFormula(check, 1, -4150)
                        check = Application.ConvertFormula(check, -4150, 1, , ActiveCell.Resize(r.Rows.Count, r.Columns.Count).Cells(ii))
                        If c.FormatConditions(num).Interior.Color = rc.Interior.Color And Evaluate(check) Then result = result + IIf(v, c.Value, 1)
                    End If
                Next c
            End If
        Next i
    ElseIf f = 3 Then
        GoTo 1
3:
        If f = 3 Then GoTo 2
    End If

    ColorEA = result
End Function

[HansV]

I cannot get that function to return a correct result...

[YasserKhalil]

It is working for me ... Can you upload your workbook so as to see the results?

[HansV]

Here you go:

ColorEA.xlsm

[YasserKhalil]

I have edited my previous post .. Please check again the UDF

[HansV]

This one works, but as far as I can tell it only returns a correct result for the simplest of conditional formatting types: where a cell is colored if its value is equal to a fixed value. It fails for all other types of conditional formatting: not equal to, greater than, greater than or equal to, less than, less than or equal to, between, not between, top n, bottom n, unique values, duplicate values, using a formula, etc.

[YasserKhalil]

Thanks a lot for explanation
Would you upload sample of another non-working file ..?

[HansV]

Why don't you test it yourself?

[YasserKhalil]

OK my tutor. I will try to test the UDF again on some other conditional formatting examples
I just wanted you to attach excellent example so as to try to recognize the errors...

[HansV]

Take the sample workbook that I attached higher up.
Replace the UDF with the newer version that you posted.
Change the conditional formatting rule for the cells in column A.
Click on the cell with the formula and press F9 to recalculate it.

[Kalpesh]

Old thread but I found a solution (for those that may see that thread)

Code: Select all

Function ColorEA(rng As Range, rc As Range, Optional f As Byte = 3, Optional v As Boolean = 1) As Single
'Programming By Eslam Abdullah
'=ColorEA($E$17:$F$20,C21,,0) >> Count
'=ColorEA($E$17:$F$20,C21,,1) >> SUM
'-------------------------------------
    Dim r As Range, c As Range, result!, check$, num&, ii&, i&

    Application.Volatile

    If f = 1 Then
1:
        For Each c In rng
            If c.Interior.Color = rc.Interior.Color Then result = result + IIf(v, c.Value, 1)
        Next c
        GoTo 3
    ElseIf f = 2 Then
2:
        For i = 1 To rng.FormatConditions.Count
            If rng.FormatConditions(i).Interior.Color = rc.Interior.Color Then
                Set r = Range(rng.FormatConditions(i).AppliesTo.Address)
                ii = (rng.Row - r.Row) * r.Columns.Count + (rng.Column - r.Column)
                For Each c In rng
                    If c.FormatConditions.Count Then
                        num = Application.Min(c.FormatConditions.Count, i)
                        ii = ii + 1
                        check = IIf(rng.FormatConditions(i).Type = 1, rng.Cells(1).Address(0, 0) & rng.FormatConditions(i).Formula1, rng.FormatConditions(i).Formula1)
                        check = Application.ConvertFormula(check, 1, -4150)
                        check = Application.ConvertFormula(check, -4150, 1, , ActiveCell.Resize(r.Rows.Count, r.Columns.Count).Cells(ii))
                        If c.FormatConditions(num).Interior.Color = rc.Interior.Color And Evaluate(check) Then result = result + IIf(v, c.Value, 1)
                    End If
                Next c
            End If
        Next i
    ElseIf f = 3 Then
        GoTo 1
3:
        If f = 3 Then GoTo 2
    End If

    ColorEA = result
End Function

How do I use this code to count the number of cells which have been conditionally formatted as a red color?

[HansV]

Welcome to Eileen's Lounge!

Let's say that the range to inspect is A1:A10. The conditional formatting rules applied to this range must all be of the type "format the cell if its value is equal to a specific value". The ColorEA function won't work for other types of conditional formatting rules.

Select another cell, say C1, and set the fill color to red.
The following formula will return the number of cells in the range A1:A10 whose fill color has been set to red as the result of the conditional formatting rule:

=ColorEA(A1:A10,C1,2,0)

A1:A10 is the range
C1 is the cell that has the color to look for.
2 means look at conditional formatting only (1 means look at direct formatting, 3 means look at both direct and conditional formatting)
0 means count (1 means sum).

In the screenshot below, cells with a value of 5 have been highlighted.

S3235.png

[Kalpesh]

What other types of conditional formatting rules there are? I am using the built-in excel rule to color a cell.

[HansV]

If you click on the Conditional Formatting button on the Home tab of the ribbon, you'll see lots of options - here are the first two submenus:

S3236.png

S3237.png

The "Equal To..." rule is the 4th one from the top in the first submenu. It's only one of the many options...