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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Wayne Taylor (webtubbs)Commented:
Are you trying to count cells coloured by conditional formatting?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roger HardyAuthor Commented:
Yes.
0
Wayne Taylor (webtubbs)Commented:
Ahh, then you can't directly get the colour. Instead, why not use COUNTIF using the same conditions as the conditional formatting?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roger HardyAuthor Commented:
duh...thank you.
0
Wayne Taylor (webtubbs)Commented:
Answer provided but not accepted by author
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.