UDF that can change values in cells other than the cell in which the UDF is used

[DocAElstein]

“AdeelSolution2” ( )

(A Final Simplified Solution)

There is not much to do.

I am not interested in the code lines to put the result in a different cell , ( although they are still working : )

So this looks like our final solution.

'   '   From  Adeel  spreadsheet    '  =sum_color(D6:G15;C17)    '    http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
' First thing
Function DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
 Dim vTemp As Variant
 Let vTemp = Evaluate("='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.SomeColor(" & RngA.Address & ", " & RngB.Address & ")")      '
' Let vTemp = Evaluate("SomeColor(" & RngA.Address & ", " & RngB.Address & ")")  ' Shortened version relying on default
 Let DoSomeColor = vTemp
End Function


' Second thing
Function SomeColor(ByVal RgA As Range, RgB As Range) As String
Stop  '  It wont
Dim Vee As Long, Sea As Range
    For Each Sea In RgA
        If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
         Let Vee = Vee + Sea.Value
        Else
        End If
    Next Sea
' Let RgB.Offset(1, 2).Value = ""
' Let RgB.Offset(1, 2).Value = Vee
 Let SomeColor = Vee
End Function

That’s it

---

Here is a simplified version for clarity to post in a forum Thread

'   '                          https://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used?p=18456&viewfull=1#post18456          https://www.excelfox.com/forum/showthread.php/2831-UDF-that-can-change-values-in-cells-other-than-the-cell-in-which-the-UDF-is-used/page2#post18456
' First thing
Function DoSum_Colour(ByVal RngA As Range, ByVal RngB As Range) As String       '    From  Adeel  spreadsheet    '  =sum_color(D6:G15;C17)    '    http://www.eileenslounge.com/viewtopic.php?p=300075#p300075
 Let DoSum_Colour = Evaluate("Sum_Colour(" & RngA.Address & ", " & RngB.Address & ")")
End Function
' Second thing
Function Sum_Colour(ByVal RgA As Range, RgB As Range) As String
Dim Vee As Long, Sea As Range
    For Each Sea In RgA
        If Sea.DisplayFormat.Interior.ColorIndex = RgB.DisplayFormat.Interior.ColorIndex Then
         Let Vee = Vee + Sea.Value
        Else
        End If
    Next Sea
 Let Sum_Colour = Vee
End Function