Function to return range for future processing

[Rolly_Sefu]

Hello,

I have created a function that return the cell color

Function GetFillColor(WorkRng As Range)
    GetFillColor= WorkRng.Interior.ColorIndex
End Function

This works perfectly.

I need to convert this to return a range of numbers ex: "{1;2;3;4}" ( its a if you select a range and hit F9 ), in order to apply the formula: "=Max((GetFillColor(A1:A5)=15)*ROW(A1:A5))" in order the get the last specific colored cell.

Anyone have an idea ?
Thank you.

 

[GlennUK]

Have a read of this ... basically, create an array of the results, and assign to function name ... you would process the WorkRng for each cell. Link: Create VBA Function to Return Array

 

[Rolly_Sefu]

Hello, just looked at the link you provided, but does not help me, i need whole array to stay in the formula: "={15;-4142;15;-4142}" so i can apply my next formula

 

[Fluff]

How about

Function GetFillColor(WorkRng As Range) As Variant
   Dim Cl As Range
   Dim i As Long
   ReDim Ary(1 To WorkRng.Count, 1 To 1)
   
   For Each Cl In WorkRng
      i = i + 1
      Ary(i, 1) = Cl.Interior.ColorIndex
   Next Cl
   GetFillColor = Ary
End Function

 

[Rolly_Sefu]

Works perfectly. Thank you

 

[GlennUK]

That's the method in the link I provided. (shrug)

 

[Fluff]

Glad we could help & thanks for the feedback.