Count color cells in conditional formating

[Thomas Kuriakose]

Respected Sirs,

We copied the below code for counting colored cells with conditional formatting, it works for cells where there is fill color but not when the same cells have conditional formatting.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
    If SUM = True Then
      For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell) + vResult
        End If
      Next rCell
    Else
        For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
        End If
      Next rCell
End If
ColorFunction = vResult
End Function

Kindly let me know how to get this working through VBA.

Please find attached the file for your reference.

Thank you very much,

with regards,
thomas

 

[p45cal]

Try (untested):

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
  For Each rCell In rRange
    If rCell.DisplayFormat.Interior.ColorIndex = lCol Then
      vResult = WorksheetFunction.SUM(rCell) + vResult
    End If
  Next rCell
Else
  For Each rCell In rRange
    If rCell.DisplayFormat.Interior.ColorIndex = lCol Then
      vResult = 1 + vResult
    End If
  Next rCell
End If
ColorFunction = vResult
End Function

 

[Chihiro]

FYI - DisplayFormat is available starting Excel 2010.

If working with older versions, it's much easier to use formula/logic used for CF in a formula to do your calculation.

 

[Thomas Kuriakose]

Respected Sir,

The code when used is giving #VALUE! error.



Kindly let me know where I am going wrong.

thank you very much,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Chihiro,

Thank you for your guidance. Kindly advise on the formula in CF to get the count result.

Thanks,

with regards,
thomas

 

[Chihiro]

Depends on your CF formula.
So just check for whatever your formula is checking for in CF and apply that in your addition calculation.

In your case, use SUMIFS() since your CF is simple value between x & y.

 

[Thomas Kuriakose]

Respected Sir,

The CF we have used does not have a formula, it is Format cells that contain a value between.


Kindly let me know how to add in this case.

Thank you very much,

with regards,
thomas

 

[Chihiro]

=SUMIFS($A$2:$A$9,$A$2:$A$9,">=76",$A$2:$A$9,"<=100")

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this formula.

There are two more conditions in this CF-

1. 0 to 50 - Red
2. 51 to 75 - Yellow

Thanks once again,

with regards,
thomas

 

[Chihiro]

So just modify ">=76" with lower limit and "<=100" with upper limit.

 

[p45cal]

Respected Sir,

The code when used is giving #VALUE! error.

View attachment 35969

Kindly let me know where I am going wrong.

You're not! It seems that DisplayFormat does not work with functions called from a sheet!! It does work if the function is called from the Immediate pane or from a Sub.
Ridiculous.
See accepted solutions here (which aren't solutions):
https://www.experts-exchange.com/qu...ined-Object-Defined-error.html#answer36152029

and see in the remarks section here:
https://msdn.microsoft.com/en-us/library/office/ff838814(v=office.15).aspx
"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."

where their suggested workaround: "To work around this issue, remove DisplayFormat from the code" is ridiculous because it won't work with conditionally formatted cells which is the main reason for introducing DisplayFormat in the first place: from Excel help: "Actions such as changing the conditional formatting or table style of a range can cause what is displayed in the current user interface to be inconsistent with the values in the corresponding properties of the Range object. Use the properties of the DisplayFormat object to return the values as they are displayed in the current user interface."

Follow Chihiro's advice or write a vba routine using DisplayFormat to place the values there.

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for your time and the solutions provided.

Much appreciated,

with regards,
thomas

 

[Nebu]

Hi:

You can define named range using Get.Cell and count the cell as per color. Find the attached.

Thanks

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution. Kindly let me know whether this applies to conditional format cells also.

Thank you very much once again.

with regards,
thomas

 

[Nebu]

Hi:

Sorry I don't think this method will work for conditional formatting, I was just going by the file you had attached in OP.

Thanks

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this information.

with regards,
thomas