Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

In support of this Forum Post
https://eileenslounge.com/viewtopic....a25802#p300075

I am not to sure if I have a possible solution here, but have some ideas.
This post has some background testing that might help me decide and / or help me get to a possible solution.

This is the problem

why Function isn't working and i also want to know how i can trouble shoot it in UDF

Option Explicit
Function sum_color(a As Range, b As Range) As Long
Dim v As Long,  c As Range
  For Each c In a
    If c.DisplayFormat.Interior.ColorIndex = b.DisplayFormat.Interior.ColorIndex Then
    v = v + c.Value
    End If
  Next c

sum_color = v
End Function

Part of Hans immediate answer was https://eileenslounge.com/viewtopic.php?p=300076#p300076] DisplayFormat cannot be used in a UDF [/url]

That spiked my interest and thoughts into things to do with UDFs not liking doing things with Cells other than the cell they are in.
DisplayFormat does not even work in my mostly older Excel versions, such as my older Excel 2007, so first on a Excel 2013 I checked that out. That is mainly what this post is about: trying it out in a slightly newer Excel 2013

Her is the initial results showing , as the OP said, that the UDF in the spreadsheet isn’t working:


I changed the OPs function a bit, as well as trying it as a simple sub routine, and tried calling both the function and the sub routine from coding
Here my coding versions:

Option Explicit
Sub CallEm()
 '   From  Adeel  spreadsheet    '  =sum_color(D6:G15;C17)    '    http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
Dim ForSubAnswer As Long
 Call Subsum_color(Range("D6:G15"), Range("C17"), ForSubAnswer)
Debug.Print ForSubAnswer       ' 54
 
Dim ForFunctionAnswer As Long
 Let ForFunctionAnswer = sum_color(Range("D6:G15"), Range("C17"))
Debug.Print ForFunctionAnswer  '  54
End Sub
Public Sub Subsum_color(ByVal a As Range, ByVal b As Range, ByRef RefBack As Long)
Dim v As Long, c As Range
    For Each c In a
        If c.DisplayFormat.Interior.ColorIndex = b.DisplayFormat.Interior.ColorIndex Then
         Let v = v + c.Value
        Else
        End If
    Next c
'Let sum_color = v
 Let RefBack = v
End Sub
Public Function sum_color(a As Range, b As Range) As Long
Dim v As Long, c As Range
    For Each c In a
        If c.DisplayFormat.Interior.ColorIndex = b.DisplayFormat.Interior.ColorIndex Then
         Let v = v + c.Value
        Else
        End If
    Next c
 Let sum_color = v
End Function

Both the sub routine and the function seem to work in normal coding.


Conclusion
As I was thinking, the problem seems to be something to do with a UDF not liking interacting with other cells in a worksheet