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

 

[Joe4]

The #NAME? error seems to suggest that it does not recognize the function name.
Which module did you place your VBA code in?
What is the name of the module?

 

[Dancarro]

The #NAME? error seems to suggest that it does not recognize the function name.
Which module did you place your VBA code in?
What is the name of the module?

The name of the module is 'Module1' but I wanted to avoid the cell entry but have the vba go the last row in AI and input the text color in column AJ

 

[Joe4]

So, are you getting the error you mentioned when you enter the formula:

=GetFontColor(AI3)

into a cell?

It seems to work for me.

Did you place the function in a module in the Workbook you are working in, or in a different workbook?

If you go to any cell, and being typing in the function (i.e. starting typing "=Get"), does it show the name of the function in the auto-complete options, like this?

If it does not, it is not seeing your function, suggesting that it is not in the workbook you are working in.

 

[Dancarro]

So, are you getting the error you mentioned when you enter the formula:

=GetFontColor(AI3)

into a cell?

It seems to work for me.

Did you place the function in a module in the Workbook you are working in, or in a different workbook?

If you go to any cell, and being typing in the function (i.e. starting typing "=Get"), does it show the name of the function in the auto-complete options, like this?
View attachment 120411

If it does not, it is not seeing your function, suggesting that it is not in the workbook you are working in.

Hi Joe,

Ok, I got it working but isn't there an easier way to extract the text color instead of formula in the cell.

In the highlighted the font color indicates 3 but there's nothing red.
Question: So is white and black = 1?

 

[Joe4]

"1" is the default font color (black).

One issue with using this User Defined Function approach is that that value is not automatically updated as you update the cell. You would actually need to re-enter the the formula, or enter a value somewhere else. That is because simply changing the color of a cell does not trigger any re-calculation.

I believe the only other way to get the font color of a cell is VBA a VBA procedure instead of a function. I am not aware of any native Excel functions that would do it. Native Excel functions primarily look at cell's values, not their formatting.

Out of curiosity, why are you trying to get the color index of the cell and return it to a cell on your worksheet?
If we have a better idea of what you are trying to do and why, we may be able to suggest other alternatives.

 

[Dancarro]

"1" is the default font color (black).

One issue with using this User Defined Function approach is that that value is not automatically updated as you update the cell. You would actually need to re-enter the the formula, or enter a value somewhere else. That is because simply changing the color of a cell does not trigger any re-calculation.

I believe the only other way to get the font color of a cell is VBA a VBA procedure instead of a function. I am not aware of any native Excel functions that would do it. Native Excel functions primarily look at cell's values, not their formatting.

Out of curiosity, why are you trying to get the color index of the cell and return it to a cell on your worksheet?
If we have a better idea of what you are trying to do and why, we may be able to suggest other alternatives.

Joe4,

My intention was for a VBA, I did a bit of research but there isn't much to get on. So the database have several columns, but just focusing on four of these. These columns might change, therefore the importance of the colour; I need to focus on blue and red only which are the key colors. There might be items in black but these are to be ignored as we shall only focus on blue and red items. I need to identify the colour in order to run a different software which doesn't identify the text formatting colour.

 

[Joe4]

Please walk me through exactly what you are going to be doing with these rows after you identify them.
If you can just walk me through a cycle in plain English of what should be happening (and what exactly you intend to do with it), I think we will have some better suggestions.

For example, the explanation might look something like this:
"Loop through all the data in columns A:D, and if the color code is 123, copy this cell over to SheetB in column A, etc".

Also, what would be helpful it to know WHEN you need this to run (do you want it to be manual or automated?), and what happens when cell colors change (do you want to run it right away, or will you manually kick it off when you are done making updates)?

The more detail you can provide, the more we can tailor the solution to do exactly what you want/need.

 

[Dancarro]

Please walk me through exactly what you are going to be doing with these rows after you identify them.
If you can just walk me through a cycle in plain English of what should be happening (and what exactly you intend to do with it), I think we will have some better suggestions.

For example, the explanation might look something like this:
"Loop through all the data in columns A:D, and if the color code is 123, copy this cell over to SheetB in column A, etc".

Also, what would be helpful it to know WHEN you need this to run (do you want it to be manual or automated?), and what happens when cell colors change (do you want to run it right away, or will you manually kick it off when you are done making updates)?

The more detail you can provide, the more we can tailor the solution to do exactly what you want/need.

So, very simple, in column 'AI' identify the colour of text cell until the last row and in column AJ state its respective colour.
Column AJ will be on a template, so when the data arrives, just place it here and click run a button and column AJ will be populated

 

[Joe4]

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?