Unable to properly call function from Excel cell and return value of the function to that cell.

Keep getting "#Value" returned to the excel cell.

In the cell I have:

=ColorFunction (I2, H16:H26)

In the vba module I have

Option Explicit

Function ColorFunction(CellColor As Range, CountRange As Range)

Dim errMsg As String
Dim myCell As Range
Dim iCol As Long
Dim cCol As Long
Dim myTotal

myTotal = 0

On Error GoTo ErrHandler

iCol = CellColor.Interior.Color

For Each myCell In CountRange

cCol = myCell.DisplayFormat.Interior.Color ' <---breaking right here
    If cCol = iCol Then
            myTotal = myTotal + 1
        End If
    Next myCell
ColorFunction = myTotal

Exit Function

If Err.Number <> 0 Then
  errMsg = "Error number: " & Str(Err.Number) & vbNewLine & _
           "Source: " & Err.Source & vbNewLine & _
           "Description: " & Err.Description
  MsgBox errMsg
  Debug.Print "CellColor = " & CellColor
  Debug.Print "iCol = " & iCol
  Debug.Print "myCell = " & myCell
  Debug.Print "cCol = " & cCol
  Debug.Print myTotal
End If

End Function

Debug returns:

CellColor = Greater than 21 Days  (Note: this is the content of cell I2)
iCol = 14348258  (Note: this is correct color for the cell I2)
myCell = 12/7/2017 (Note: this is the content of cell H16
cCol = 0  (Note: ...this is the problem...)

I have run the code a macro and stepped through it with F8 and all the values return correctly.  However, when I call the function from within the cell of the worksheet, I get the error.
Roger HardyAsked:
Who is Participating?
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
Ahh, then you can't directly get the colour. Instead, why not use COUNTIF using the same conditions as the conditional formatting?
Wayne Taylor (webtubbs)Commented:
Use this...


...instead of this...

Roger HardyAuthor Commented:
Returns "0"

I know there are 7 cells with the same interior color.  I checked the numbers and the RGB settings multiple times.

I also tried myCell.interior.color before and got the same result
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Wayne Taylor (webtubbs)Commented:
Are you trying to count cells coloured by conditional formatting?
Roger HardyAuthor Commented:
Roger HardyAuthor Commented:
duh...thank you.
Wayne Taylor (webtubbs)Commented:
Answer provided but not accepted by author
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.