Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF function
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? |
#2
|
|||
|
|||
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. On Fri, 7 Jan 2005 20:21:02 -0800, "esha734" wrote: 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? |
#3
|
|||
|
|||
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 InsertNameDefine 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","neit her")) 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 -----Original Message----- 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? . |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Since Excel don=C2=B4t recognize Colours as "necessary to=20
recalculate", add Now(): =3DIF(Now()*0+FontColor(A1)=3D3,"Red","Black") How does NOW() affect the calculation? NOW() doesn't cause a calculation. This will have no=20 effect in the formula. Are you thinking that because NOW()=20 is volatile this causes a calculation? It doesn't. Biff -----Original Message----- Hi, I've just learned this by Arvi. Include Only this=20 VBA/Macro: Public Function FontColor(MyCell As Range) As Variant FontColor =3D MyCell.Font.ColorIndex End Function Then use the new Function:=20 =3DFontColor(A1) Since Excel don=C2=B4t recognize Colours as "necessary to=20 recalculate", add Now(): =3DIF(Now()*0+FontColor(A1)=3D3,"Red","Black") Ola . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to list unique values | Excel Worksheet Functions | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions | |||
change function variable prompts?? | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions |