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

ErrHandler:
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...)
 0

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?
0
 
Wayne Taylor (webtubbs)Commented:
Use this...

myCell.Interior.Color

...instead of this...

myCell.DisplayFormat.Interior.Color
0
 
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
0
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?
0
 
Roger HardyAuthor Commented:
Yes.
0
 
Roger HardyAuthor Commented:
duh...thank you.
0
 
Wayne Taylor (webtubbs)Commented:
Answer provided but not accepted by author
0
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.