johnhill2312
asked on
excel - apply text color in IF statement
Hello Experts
I am commuting some values in Excel using quite a few IF statements, I want to apply different colors to the text to highlight the affected columns.
Please can someone advise how can I set text color using Formula in Excel?
Thanks
I am commuting some values in Excel using quite a few IF statements, I want to apply different colors to the text to highlight the affected columns.
Please can someone advise how can I set text color using Formula in Excel?
Thanks
You can't actually affect the font colour using the formula within the cell but you can use Conditional Formatting (CF) to affect the font colour of a cell. The conditions in the CF can be formula driven.
Thanks
Rob H
Thanks
Rob H
ASKER
Hello both, thanks for your input
I have this formula in place, if the condition satisfies then I want to set the AVERAGE in RED.
IF(inventory!RC=0,AVERAGE( inventory! RC2:RC13), 0)
Thanks
I have this formula in place, if the condition satisfies then I want to set the AVERAGE in RED.
IF(inventory!RC=0,AVERAGE(
Thanks
I assume this is the formula in the cell.
For one, it does not make sense. The "Inventory!RC=0" is missing a row number. What are you tring to achieve with the formula.
What condition do you need to be satisfied for font to go Red? Equal to zero or not equal to zero?
Thanks
Rob H
For one, it does not make sense. The "Inventory!RC=0" is missing a row number. What are you tring to achieve with the formula.
What condition do you need to be satisfied for font to go Red? Equal to zero or not equal to zero?
Thanks
Rob H
Hi,
if you want to change in red the text appearing where this formula iss
Use This formula in the Conditional Formatting ( In Start Conditional Formatting / New Rule / Formula ...)
like in the example
Regards
EE20140416.xlsx
if you want to change in red the text appearing where this formula iss
IF(inventory!RC=0,AVERAGE(inventory!RC2:RC13),0)
Use This formula in the Conditional Formatting ( In Start Conditional Formatting / New Rule / Formula ...)
IF(inventory!RC=0,1,0)
and use the format you needlike in the example
Regards
EE20140416.xlsx
ASKER
Hi,
Please check the report sheet, I am checking the values from the previous 2 sheets and writing down the Average if the Sheet2 cell=0
I have marked one of the value to RED manually, I want to apply the RED font using the formula.
thanks
stock-zero-coz-oos-or-new-exampl.xlsx
Please check the report sheet, I am checking the values from the previous 2 sheets and writing down the Average if the Sheet2 cell=0
I have marked one of the value to RED manually, I want to apply the RED font using the formula.
thanks
stock-zero-coz-oos-or-new-exampl.xlsx
ASKER
Hi Rob
I want to check if RC not equal to zero OR if its null, then I want the font to go RED.
Thanks
I want to check if RC not equal to zero OR if its null, then I want the font to go RED.
Thanks
ASKER
Hi Rgonzo1971
Thanks for the attachment, am sorry but cannot see your changes? Please can you let me know what did you change or please upload the file again.
Many thanks
Thanks for the attachment, am sorry but cannot see your changes? Please can you let me know what did you change or please upload the file again.
Many thanks
Apologies about the RC comment earlier, hadn't realised you were using R1C1 style references.
I will take a look at the Conditional Formatting.
I will take a look at the Conditional Formatting.
Before seeing your comment if RC is equal to zero
Now on the report sheet the value is equal to zero and the one where there is zero in the inventory sheet are in red
Now on the report sheet the value is equal to zero and the one where there is zero in the inventory sheet are in red
ASKER
still dont see the changes Rgonzo1971 - please see the screenshot...
am i missing something?
screen.png
am i missing something?
screen.png
I have downloaded Rgonzo's file and I see the Conditional Formatting applied.
Slight change suggested for formula in CF, it doesn't need to be enclosed in an IF statement; it can just be:
=OR(RC=0,inventory!RC=0)
CF looks for a True or False result, 1 = True and 0 = False in your IF statement but as the OR statement gives a True or False anyway, the IF isn't required.
Do you also need to make Red if Sales History is zero?
Thanks
Rob H
Slight change suggested for formula in CF, it doesn't need to be enclosed in an IF statement; it can just be:
=OR(RC=0,inventory!RC=0)
CF looks for a True or False result, 1 = True and 0 = False in your IF statement but as the OR statement gives a True or False anyway, the IF isn't required.
Do you also need to make Red if Sales History is zero?
Thanks
Rob H
I forgot Excel does not translate formulas in the conditional formatting ( I have another Language version)
pls try this
Regards
stock-zero-coz-oos-or-new-V3.xlsx
pls try this
Regards
stock-zero-coz-oos-or-new-V3.xlsx
ASKER
Hi Rob
>>Do you also need to make Red if Sales History is zero?
Yes please
not sure why I am unable to see the changes you are sending Rgonzo1971
>>Do you also need to make Red if Sales History is zero?
Yes please
not sure why I am unable to see the changes you are sending Rgonzo1971
New formula
Taking account if Sales History is zero
=(B2=0)+(inventory!B2=0)+( 'prod-sale s-history' !B2=0)
Regards
stock-zero-coz-oos-or-new-V5.xlsx
Taking account if Sales History is zero
=(B2=0)+(inventory!B2=0)+(
Regards
stock-zero-coz-oos-or-new-V5.xlsx
ASKER
Hi Rgonzo1971
Thanks again for your reply
Please can you write down your steps ... I mean how can I reproduce the results you are explaining.
I cannot see anything under Condtional Formatting ->Manage rules ??
Thanks again for your reply
Please can you write down your steps ... I mean how can I reproduce the results you are explaining.
I cannot see anything under Condtional Formatting ->Manage rules ??
Hi,
At best you select the Cell B2 in the report sheet
Then goto Home / Styles / Conditional Formatting / Manage Rules / Add (in this case Edit) Rule / Use a Formula...
Place the formula in the Textbox Click Format to see what is the applied format to the condition
Regards
At best you select the Cell B2 in the report sheet
Then goto Home / Styles / Conditional Formatting / Manage Rules / Add (in this case Edit) Rule / Use a Formula...
Place the formula in the Textbox Click Format to see what is the applied format to the condition
Regards
ASKER
Hi Rgonzo
Thanks again, when I followed the steps mentioned above, I am getting an error, attached.
Please advice
screen.png
Thanks again, when I followed the steps mentioned above, I am getting an error, attached.
Please advice
screen.png
Hi,
I forgot you have XL2007 it means you cannot use in a formula a different worksheet but
there is a workaround with Named Ranges
I created ProdRange and InventoryRange by going to Formulas Defined Names Define Name
Refers to ='prod-sales-history'!$B$2 :$M$5 & =inventory!$B$2:$M$5 respectively
then used
this formula in the Conditional Formatting
=(B2=0)+(OFFSET(ProdRange, ROW(B2)-2, COLUMN(B2) -2,1,1)=0) +(OFFSET(I nventoryRa nge,ROW(B2 )-2,COLUMN (B2)-2,1,1 )=0)
see above to insert it
Regards
stock-zero-coz-oos-or-new-V6.xlsx
I forgot you have XL2007 it means you cannot use in a formula a different worksheet but
there is a workaround with Named Ranges
I created ProdRange and InventoryRange by going to Formulas Defined Names Define Name
Refers to ='prod-sales-history'!$B$2
then used
this formula in the Conditional Formatting
=(B2=0)+(OFFSET(ProdRange,
see above to insert it
Regards
stock-zero-coz-oos-or-new-V6.xlsx
ASKER
Thank you again for your help, really appreciate it.
I am now trying to figure what is going wrong in the attached, as adviced I have created Range names and used it in the formula, however its applying RED font to even CELLS which does not qualify the statement.
Please can you advice?
Many thanks
stock-zero-coz-oos-or-new-V5-1.xlsx
I am now trying to figure what is going wrong in the attached, as adviced I have created Range names and used it in the formula, however its applying RED font to even CELLS which does not qualify the statement.
Please can you advice?
Many thanks
stock-zero-coz-oos-or-new-V5-1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh great!! I got it finally. I really hope when I take this across my real and very complicated sheet - it works!!!
I am sure it will as you have explained it really very well!!
Many thanks again for your help and time!!
Really appreciate it.
Have a good weekend!!
I am sure it will as you have explained it really very well!!
Many thanks again for your help and time!!
Really appreciate it.
Have a good weekend!!
ASKER
Many thanks...
ASKER
hi Rgonzo1971
please can you visit: https://www.experts-exchange.com/questions/28418455/excel-apply-text-color-in-IF-statement.html
I am having a small problem in the formula again... please can you check?
please can you visit: https://www.experts-exchange.com/questions/28418455/excel-apply-text-color-in-IF-statement.html
I am having a small problem in the formula again... please can you check?
Could you sample of what you want to do?
Regards