title: Getting #Value! Error on worksheet, but VBA test sub returns what it should
body: <p>I have tried to use several posts I found in regards to using DisplayFormat within a UDF that loops. I am trying to count conditionally formatted cells. I can return the correct count in a test sub, but get a #VALUE! error in my worksheet.</p>
  
  <p>I have tried many things, but the one I thought was most relevant was from a response by Tim Williams:</p>
  
  <p><a href="https://stackoverflow.com/questions/52265819/getting-cell-interior-color-fails-when-range-passed-from-worksheet-function">Getting cell Interior Color Fails when range passed from Worksheet function</a></p>
  
  <p>However, the EVALUATE method would cause "Error 2015" values and I was unable to get around that problem, I saw this issue while using the test sub routine. </p>
  
  <p>WORKING ANSWER thanks to @BigBen:</p>
  
  <pre><code>Public Function DFColor(addr)
  DFColor = Range(addr).DisplayFormat.Interior.ColorIndex
  End Function
  
  Function COUNTBYCOLOUR(CountRange As Range)
  Dim countColour As Integer
  Dim rCell As Range
  countColour = 3
  For Each rCell In CountRange
     If countColour = (rCell.Parent.Evaluate("DFColor(""" &amp; rCell.Address &amp; """)"))Then
      COUNTBYCOLOUR = COUNTBYCOLOUR + 1
      End If
  Next rCell
  End Function
  </code></pre>
  
  <p>Previous attempt Main Function: </p>
  
  <pre><code>Function COUNTBYCOLOUR(CountRange As Range)
  'make workbook recalculate on cell changes
  Application.Volatile
  'Declaring variables
  Dim countColour As Integer
  Dim rCell As Range
  'Setting search colour (red)
  countColour = 3
  'Looping through the defined range
  For Each rCell In CountRange
  Dim Y As Integer
  Y = evalCell(rCell)
  'Getting the count of matching colored cells
  If Y = countColour Then
          COUNTBYCOLOUR = COUNTBYCOLOUR + 1
  End If
  Next rCell
  End Function
  
  'USE TO TEST UDF, Place cursor before Sub test() and press f8 to step through and debug
  'Sub test()
   'MsgBox COUNTBYCOLOUR(Range("C4:C11"))
  'End Sub
  </code></pre>
  
  <p>Previous attempt Secondary function to try and separate DisplayFormat from UDF: </p>
  
  <pre><code>'get cell colour outside of COUNTBYCOLOUR UDF so it evaluates DisplayFormat on worksheet.
  Public Function evalCell(currentCell)
      evalCell = currentCell.DisplayFormat.Interior.ColorIndex
  End Function
  </code></pre>
  
  <p>When I run the sub routine at the bottom of my main function, I get the correct count (only trying to count red cells, no need to have a colour reference cell). When I use COUNTBYCOLOUR in my worksheet, I get the #VALUE! error. Hopefully some more experienced eyes can help me. Thank you.</p>