Can I Query Font Color of Cell

[BobW]

Hello,

Trying to query the font color of a cell. If my font color is red, I want to return the number 12. If font color is black (or auto), I want to return the number 4. Is this possible? Example below shows the result of querying the font color in Column A (result would be returned in column B)

	A	B	C	D	E	F
1​	This is a red font text cell	12
2​	This is a black font text cell	4
3​	This is another red font text cell	12

I run O365 Excel/Windows

 

[6StringJazzer]

It is possible only with VBA. What are the color numbers for your version of red?

 

[BobW]

It is possible only with VBA. What are the color numbers for your version of red?

Hi 6StringJazzer. Thanks much! I think I could alter the if to "not black" -- i just picked the red font from the font palette. Does that help?

 

[6StringJazzer]

Put this code into a standard module (like Module1) and use it in a sheet like this:

Public Function FontNotBlack(R As Range) As Boolean

   FontNotBlack = R.Font.Color <> RGB(0, 0, 0)

End Function

$scratch.xlsm
	A	B
1	test	FALSE
2	test	FALSE
3	test	TRUE
4	test	FALSE
5	test	FALSE
6	test	TRUE
7	test	FALSE
8	test	FALSE
9	test	FALSE
Sheet17

Cell Formulas
Range	Formula
B1:B9	B1	=FontNotBlack(A1)

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B
1
2	This is a red font text cell	12
3	This is a black font text cell	4
4	This is another red font text cell	12
5	This is automatic font colour	4
Data

Cell Formulas
Range	Formula
B2:B5	B2	=IF(FontColour=0,1,FontColour)*4

Where FontColour is a defined name using

=GET.CELL(24,INDIRECT("rc[-1]",FALSE))

This will work for any font colour, but can be limited to just red & black.

 

[6StringJazzer]

I did not know that was available. I normally don't think about Excel 4.0 macros. It might have to be explicitly enabled in the Trust Center in the current version of Excel.

 

[Fluff]

It might have to be explicitly enabled in the Trust Center in the current version of Excel.

Doesn't seem to need it. I don't have that checked, but it still works, which does seem a bit odd.

 

[BobW]

Put this code into a standard module (like Module1) and use it in a sheet like this:

Public Function FontNotBlack(R As Range) As Boolean

   FontNotBlack = R.Font.Color <> RGB(0, 0, 0)

End Function

$scratch.xlsm
	A	B
1	test	FALSE
2	test	FALSE
3	test	TRUE
4	test	FALSE
5	test	FALSE
6	test	TRUE
7	test	FALSE
8	test	FALSE
9	test	FALSE
Sheet17

Cell Formulas
Range	Formula
B1:B9	B1	=FontNotBlack(A1)

Hi Jeff,

Thanks so much. This works!

 

[BobW]

Doesn't seem to need it. I don't have that checked, but it still works, which does seem a bit odd.

 

[BobW]

Hi Fluff,

Thanks so much! This solution works too!