vba extract text color format

[Dancarro]

Hi guys,

I am trying to get a vba code to extract font color and it appears not be working.

The data is in column AI and I created column AJ 'Font color' but the vba code appears not to be working

=GetFontColor(AI3)

What I need to gain is to know the text color of the specific range of column AI? Any advice would be great.
Kind Regards,
Dan

 

[Dancarro]

You could run this little procedure to populate column AJ with the font color from column AI starting row row 3 and going down to the last row with data in column A1.
Just note that if you change any of the font colors after the fact, you will need to call/run the procedure again:

Sub PopulateFontColors()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column A1
    lr = Cells(Rows.Count, "AI").End(xlUp).Row
   
'   Loop through all rows starting with row 3 and populate font color in column AJ
    For r = 3 To lr
        Cells(r, "AJ").Value = Cells(r, "AI").Font.ColorIndex
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

So does that do what you need?

Hi Joe, it works perfectly. Thank you so much

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[Dancarro]

You could run this little procedure to populate column AJ with the font color from column AI starting row row 3 and going down to the last row with data in column A1.
Just note that if you change any of the font colors after the fact, you will need to call/run the procedure again:

Sub PopulateFontColors()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row with data in column A1
    lr = Cells(Rows.Count, "AI").End(xlUp).Row
   
'   Loop through all rows starting with row 3 and populate font color in column AJ
    For r = 3 To lr
        Cells(r, "AJ").Value = Cells(r, "AI").Font.ColorIndex
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

So does that do what you need?

Hi Joe, it works perfectly. Thank you so much.