IF function

[esha734]

How do I determine the font color of a cell with an IF function?
This is how I would think its done (the following formula could be in B1):
IF A1=red,"RED","BLACK"
If the font color in A1 is red, then RED is printed in B1, otherwise BLACK
is printed in B1.
but this does not work. Any suggestions?

 

[Myrna Larson]

You would need to write a VBA macro (UDF) that returns the font color, either
as a number or as text, and check the result of that function in your IF
statement.

If the font color is determined by conditional formatting, your formula can
use the same formula that you used in CF.

 

[Biff]

Hi!

This isn't a very straight forward thing to accomplish in
Excel.

Here's one way: (I assume that you are only interested in
either RED or BLACK)

You have to create a named formula and then call that
named formula in your IF function.

Goto Insert>Name>Define

Name: TextColor

In the Refers To box enter this formula:

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

Now, in cell B1 you can enter this formula to test for red
or black text in cell A1:

=IF(TextColor=3,"red",IF(TextColor=0,"black","neither"))

Important: this formula will not automatically update
if/when the text color changes. You would have to do a
manual calculation by hitting F9 or wait until an
automatic calculation is triggered by some other event.
That's just the way it is! Can't do anything about it.

For more detailed info, see:

http://cpearson.com/excel/colors.htm

http://xldynamic.com/source/xld.ColourCounter.html

Biff

 

[Ola]

Hi,

I've just learned this by Arvi. Include Only this VBA/Macro:

Public Function FontColor(MyCell As Range) As Variant
FontColor = MyCell.Font.ColorIndex
End Function

Then use the new Function:
=FontColor(A1)

Since Excel don´t recognize Colours as "necessary to recalculate", add Now():
=IF(Now()*0+FontColor(A1)=3,"Red","Black")

Ola

 

[Biff]

Since Excel don´t recognize Colours as "necessary to

recalculate", add Now():
=IF(Now()*0+FontColor(A1)=3,"Red","Black")

How does NOW() affect the calculation?

NOW() doesn't cause a calculation. This will have no
effect in the formula. Are you thinking that because NOW()
is volatile this causes a calculation? It doesn't.

Biff