counting by conditional formatting color

[tbilderbac]

I got this code:

-----------
Public Function CountByColor2(CellRange As Range, TargetCell As Range)

Dim TargetColor As Long, Count As Long, C As Range

TargetColor = TargetCell.Interior.ColorIndex
For Each C In CellRange
If C.Interior.ColorIndex = TargetColor Then Count = Count + 1
Next C
CountByColor2 = Count


End Function
------------

I created the formula:
=CountByColor2(RC[-23]:RC[-6],R[-16]C[-25])

and wanting it to count up the RED blocks: [should be 5]

 

[tbilderbac]

I have both:

CountByColor:

Option Explicit

Public Function CountByColor(CellRange As Range, TargetCell As Range)

Dim TargetColor As Long, Count As Long, C As Range

TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
For Each C In CellRange
If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then Count = Count + 1

Next C
CountByColor = Count
End Function
Function CFColour(Cl As Range) As Double
CFColour = Cl.DisplayFormat.Interior.Color
End Function


CountByColor2:

Option Explicit

Public Function CountByColor2(CellRange As Range, TargetCell As Range)

Dim TargetColor As Long, Count As Long, C As Range

TargetColor = TargetCell.DisplayFormat.Interior.ColorIndex
For Each C In CellRange
If C.DisplayFormat.Interior.ColorIndex = TargetColor Then Count = Count + 1
Next C
CountByColor2 = Count


End Function


Which one do you want me to try in the cell?

 

[Fluff]

I am referring to the code I posted, not your code. Is it in a standard module?

 

[tbilderbac]

The code that you send I first changed it from what it was to your code in CountByColor

 

[Fluff]

Will you please answer my question.

 

[tbilderbac]

I think so, it is under Modules versus Class Modules

I think that is what you mean?

 

[Fluff]

That's fine. Is the other code I supplied in the same module?

 

[tbilderbac]

yes both are in the same module

 

[Fluff]

In that case I have no idea why it's not working.
Can you post some sample data including the formula your using.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Rick Rothstein]

I think the problem is that you cannot use DisplayFormat directly in a UDF. There is a workaround though. Remove whatever color counting functions you now have and put the following two functions in their place...

Function CountByColor(CellRange As Range, TargetCell As Range) As Long
  CountByColor = Evaluate("CountColorHelper(" & CellRange.Address & "," & TargetCell.Interior.Color & ")")
End Function

Function CountColorHelper(CellRangeToCount As Range, ColorToCount As Long) As Long
  Dim TargetColor As Long, Count As Long, C As Range
  For Each C In CellRangeToCount
    If C.DisplayFormat.Interior.Color = ColorToCount Then Count = Count + 1
  Next C
  CountColorHelper = Count
End Function

 

[tbilderbac]

Okay, let me work on that and I will let you know