How many conditional formatted cells are in the range (more colour criterias)

[Villalobos]

Hi,

I would like to ask your help regading the conditional formatted cells counting.

In range B9:C19 I applied 4 different conditional formatting rules (colours: red, orange, yellow, green) and my target is to count those cells which are formatted with red, orange and yellow.

The sample file attached with expected result.

Could you please help me to solve this issue?

Note: In the original file the range is dynamic so changing everyday.

 

[PCosta87]

Hi @Villalobos

First things first, there is no way (that I'm aware of) to do that with conditional formatted cells.
You can, however, do that for manually colored cells... you need a UDF (User defined Function) to do this (it's like a new formula created by the user)

In this case, in a new module add this:

Function CountByColor(Refcolor As Range, rng As Range)

    Application.Volatile
 
    Dim c As Range
 
    For Each c In rng
        If Refcolor.Interior.ColorIndex = c.Interior.ColorIndex Then
            CountByColor = CountByColor + 1
        End If
    Next c

End Function

Then just type it in like a normal formula... see attached

Note: this only updates when an event triggers the calculation, like if you press F9 or change any of the values in the cells.

For the UDF to calculate automatically you need to add to the respective sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.Calculate

End Sub

But there is a catch , since it triggers calculation, you lose copied content and stuff like that, so I recommend you use the UDF and force the calculation manually.

Hope it helps
Regards

 

[Villalobos]

Hi PCosta87,

Thank you the feedback, I have so much cell, the manual coloring is not possible.

Who can confirm PCosta87's statement "there is no way"?

 

[p45cal]

Hi PCosta87,

Thank you the feedback, I have so much cell, the manual coloring is not possible.

Who can confirm PCosta87's statement "there is no way"?

There should be a way, since excel2010 I think, with the new DisplayFormat property, however, MicroSoft have not implemented it well; it works in Subs, but not in a user defined Function!

So you can have a function as PCosta87 suggests as long as the actual colour of the cell is the colour being counted (not the conditional format colour).
A possible workaround might involve event code changing the actual colour of the cell to the same as the conditional format colour:
cll.Interior.Color = cll.DisplayFormat.Interior.Color

In the attached I've included some impractical code (except for small projects) where this is working after a fashion (couldn't get the sheet to recalculate in a timely way).
Formula in C26, updates on selection_change.

 

[PCosta87]

@p45cal

That may be a good work around... The only thing I would change is how to trigger the the macro to change the background color to match the displayformat.

In the example, you would need to change every value in the range for it to change all the background colors (copy/paste as values would work)... but what if we add a button to "refresh" the background colors of all cells in the range at the same time and then have the worksheet change event recalculate?

Please see attachment

Regards

 

[p45cal]

Yes, it's a good idea to initialise all the colours with sub, but since the OP says "I have so much cell" having to click a button to update values after changing a value (which in turn changes CF colours) is not something that most users would expect of a worksheet function in a cell, and could be resource-hungry/take some time. So I would want to see actual colours updating themselves and calculation updating itself as soon as one or more cells' values changed, but restricting the recolouring of cells to only those that have changed.
Having said that, the worksheet_change event's target range is only the cells which have been changed by the user or a macro, not the cells which change as a result of their formulae recalculating, so re-colouring all the relevant CF cells might be a must.
It's not easy and depends heavily on how the user wants to use it.
The problem that really needs to be addressed is how MicroSoft implements this .DisplayFormat property, because at it stands it's less than half as useful as it could be; it needs to work in Functions.

 

[Villalobos]

Hi @p45cal,

Thank you very much the .DisplayFormat tip, I was able to figure out the code.

Option Explicit
Sub CountColoredCells()
Dim a As Range
Dim Count, Lastrow1, Lastrow2 As Long
Dim b As Variant

  Count = 0
  Lastrow1 = ActiveSheet.UsedRange.Rows.Count
  
  For Each a In Range("B9:C" & Lastrow1)
  If a.DisplayFormat.Interior.Color = RGB(255, 0, 0) Then Count = Count + 1
  If a.DisplayFormat.Interior.Color = RGB(255, 192, 0) Then Count = Count + 1
  If a.DisplayFormat.Interior.Color = RGB(255, 255, 0) Then Count = Count + 1
  Next
  
  With ActiveSheet
  Lastrow2 = .Cells(.Rows.Count, "B").End(xlUp).Row + 7
  For b = 2 To 3
  If b = 2 Then
  Cells(Lastrow2, b) = "Result: "
  Else
  Cells(Lastrow2, b) = Count
  End If
  Next b
  End With

End Sub