“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
That seems to give the correct result of 54
( For comparison, see the results of 54 here:
http://www.excelfox.com/forum/showth...ll=1#post18404
)
Share ‘DisplayFormatInUDFAdeel1.xlsm’ https://app.box.com/s/5nvqh5r8pggc11ulz4lti3yhk39b9wda
Bookmarks