index match extract cells with certain font color

[hajiali]

Hello all Im looking to see how I can extract cells with certain font ColorIndex.

I have the Following Macro

Function GetFontColor(ByVal Target As Range) As Integer
    GetFontColor = Target.Font.ColorIndex
End Function

The formula i have tried did not work. in my example I'm trying to get all Names if the font color EList[[F1]:[F10] appears the 41 color index

=INDEX(EList[LAST, FIRST],AGGREGATE(15,6,(ROW(EList[LAST, FIRST])-ROW(F2)+1)/(GetFontColor(EList[[F1]:[F10]])=41),ROWS(X15:X15)))

any help is greatly appreciated.

Thanks,

 

[hajiali]

Thanks Fluff. Changing my basic Function to yours works like magic. I really Appreciate it.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[hajiali]

Hello Fluff the only issue I'm having is the formula that I have is not being updated as the data is being entered. I have made sure that the Calculation Options is set to Automatic and even when I try to hit Calculate Now it does not appear. I have to recopy the formula on the FHL sheet in order for the values to appear. not sure if the Function can be updated to fix this or is there another route.

 

[Fluff]

You can try

Function GetFontColor(ByVal Rng As Range) As Variant
    Dim i As Long, j As Long
    Dim Ary As Variant
    
    Application.Volatile
    ReDim Ary(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)
    For i = 1 To UBound(Ary)
      For j = 1 To UBound(Ary, 2)
         Ary(i, j) = Rng(i, j).Font.ColorIndex
      Next j
   Next i
   GetFontColor = Ary
End Function

But you will still have to use F9 to force a recalc.

 

[hajiali]

Thanks that will be easy fix I just included the recalculate into the coding. Works great!

 

[Fluff]

My pleasure.