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,

 

[Dan_W]

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,

Hard to help without a dataset to test it on, but which part of the formula isn't working? Have you broken it down to its constituent parts and tested each part first?

 

[hajiali]

Im certain its

(GetFontColor(EList[[F1]:[F10]])=41)

when Change that to

(EList[[F1]:[F10]]=W15)

where W15 is a date
EList[[F1]:[F10]] range are also dates. However I have designated dates with AM shift with ColorIndex = 41 and PM with colorIndex=9

using the following worksheet_change code in the sheet where EList[[F1]:[F10]]

If Not FHLCells Is Nothing And Cells(FHLCells.Row, DECLD.Column) = "FHL" And FHLCells.Value2 <> "" Then
         With UserForm4
            .Tag = "AM"
            .Repaint
            .StartUpPosition = 0
            .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
            .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
            .Show
             If .Tag = "AM" Then
                Target.Font.ColorIndex = 41
                MsgBox "AM SHIFT WAS ADDED", vbOKOnly, "AM SHIFT"
             End If
             If .Tag = "PM" Then
                Target.Font.ColorIndex = 9
                 MsgBox "PM SHIFT WAS ADDED", vbOKOnly, "PM SHIFT"
             End If
         End With
    End If

 

[Dan_W]

You don't need to be "certain" when you can test it and be "positive", but if you're using VBA, why not just use VBA to get the results?

But in any event - I tried your function and it works provided that the cells are all the same font color. If they're not, it returns an error.

 

[hajiali]

You don't need to be "certain" when you can test it and be "positive", but if you're using VBA, why not just use VBA to get the results?

But in any event - I tried your function and it works provided that the cells are all the same font color. If they're not, it returns an error.

glad you mentioned that with same font color. you are right it does work l tried that. So the point was to Distinguish between "AM" and "PM" the range will have different colors. is there any way beside VBA. the reason I wanted a formula instead of VBA is the sheet with this formula can be changed to different months and its for display/print purpose only

 

[Dan_W]

glad you mentioned that with same font color. you are right it does work l tried that. So the point was to Distinguish between "AM" and "PM" the range will have different colors. is there any way beside VBA. the reason I wanted a formula instead of VBA is the sheet with this formula can be changed to different months and its for display/print purpose only

Conditional formatting?

 

[hajiali]

Book1
	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ
1	F1	F2	F3	F4	F5	F6	F7	F8	F9	F10
2	01/28	03/01	05/06	05/07	05/10	05/11	05/12	05/13	05/14	12/12
3	01/31	04/02	05/30	06/07	06/08	06/09	08/13	09/21	09/22	09/24
4	03/09	03/10	03/11	03/12	03/04	04/01	04/02	04/16	04/09	05/02
5	01/18	01/02	10/01	10/15
6
EMPLOYEE LIST

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BQ2:BQ73	Expression	=BF2>=10	text	NO
BP2:BP73	Expression	=BF2>=9	text	NO
BO2:BO73	Expression	=BF2>=8	text	NO
BN2:BN73	Expression	=BF2>=7	text	NO
BM2:BM73	Expression	=BF2>=6	text	NO
BL2:BL73	Expression	=BF2>=5	text	NO
BK2:BK73	Expression	=BF2>=4	text	NO
BJ2:BJ73	Expression	=BF2>=3	text	NO
BI2:BI73	Expression	=BF2>=2	text	NO
BH2:BH73	Expression	=BF2>=1	text	NO

as shown above in cell BJ5 = 10/1/2022 in colorindex=41 for "AM" shift. and to the right is 10/15/22 in ColorIndex=9 for PM and we will have Black and Red fonts which needs to be ignored.

 

[hajiali]

Book1
	W	X	Y	Z	AA	AB	AC
14	DAY	FHL AM	FHL AM	FHL AM	FHL PM	FHL PM	FHL PM
15	10/1	#NUM!
16	10/2
17	10/3
18	10/4
19	10/5
20	10/6
21	10/7
22	10/8
23	10/9
24	10/10
25	10/11
26	10/12
27	10/13
28	10/14
29	10/15
FHL

Cell Formulas
Range	Formula
X15	X15	=INDEX(EList[LAST, FIRST],AGGREGATE(15,6,(ROW(EList[LAST, FIRST])-ROW(F2)+1)/(GetFontColor(EList[[F1]:[F10]])=41),ROWS(X15:X15)))
W15	W15	=DATE(F2,BF4,1)
W16:W29	W16	=W15+1

in my other sheet Im needing to Pull all agents on AM on the AM section and All PM agents on PM section . ex. Agent in Column 5 should appear on AM for 10/1 but PM for 10/15

 

[Dan_W]

Ahh I see. No conditional formatting won't help here. Personally, I would use VBA but someone might have a formula based solution. I'm racing out the door, but will check back in when get home later tonight in case no one has been able to help

 

[Fluff]

How about

Function GetFontColor(ByVal Rng As Range) As Variant
    Dim i As Long, j As Long
    Dim Ary As Variant
    
    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