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

**DocAElstein** · 10-21-2022, 05:40 PM

“AdeelSolution1” ( )

Simple modifications to the second thing , for now, just to see if we can get a result from DisplayFormat
I will forget the sheet referring, as that is not of interest to the final solution,
and
I will do some simple use of DisplayFormat, for example, the DisplayFormat.Interior.ColorIndex of cell C17

Example, change

Code:

Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "You wrote " & Rng.Value & " in cell " & Rng.Address(0, 0) & ",  in worksheet " & Sht
End Sub

to

Code:

' Second thing
Sub DoDisplayFormat(ByVal Rng As Range, ByVal Sht As String)
Stop  '  It wont
 Let Rng.Offset(0, 2).Value = ""
 Let Rng.Offset(0, 2).Value = "For cell " & Rng.Address(0, 0) & ", you have a DisplayFormat.Interior.ColorIndex of  " & Rng.DisplayFormat.Interior.ColorIndex
End Sub

This appears to be giving me sensible results in Office Excel 2013:

Towards a final solution for Adeel
We want a replacement for a formula of this form =sum_color(D6:G15,C17)
So we need
a second range argument in the first thing,
[color=blueviolet]Function[/color] DoSomeColor(ByVal RngA As Range, ByVal RngB As Range) As String
and
in the second thing we no longer need to be taking in a sheet name, but do need to take in a second range argument.
Sub SomeColor(ByVal RgA As Range, RgB As Range)

The rest of the modification is just doing something similar in the second thing to Adeel’s original function.

Code:

'   '   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
 Evaluate "='" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'!DisplayFormatUDF.SomeColor(" & RngA.Address & ", " & RngB.Address & ")"         '
' Evaluate "SomeColor(" & RngA.Address & ", " & RngB.Address & ")"  ' Shortened version relying on default
End Function


' Second thing
Sub SomeColor(ByVal RgA As Range, RgB As Range)
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
End Sub