Solved

excel - apply text color in IF statement

Posted on 2014-04-16
26
473 Views
Last Modified: 2014-04-23
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
0
Comment
Question by:johnhill2312
  • 12
  • 10
  • 4
26 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40004239
Hi,

Could you sample of what you want to do?

Regards
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40004240
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
0
 

Author Comment

by:johnhill2312
ID: 40004263
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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40004295
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40004328
Hi,

if you want to change in red the text appearing where this formula iss
IF(inventory!RC=0,AVERAGE(inventory!RC2:RC13),0)

Open in new window


Use This formula in the Conditional Formatting ( In Start Conditional Formatting / New Rule / Formula ...)

IF(inventory!RC=0,1,0)

Open in new window

and use the format you need

like in the example

Regards
EE20140416.xlsx
0
 

Author Comment

by:johnhill2312
ID: 40004334
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
0
 

Author Comment

by:johnhill2312
ID: 40004356
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40004361
HI,

Is that what you are looking for?
EDITED

Version2
Regards
stock-zero-coz-oos-or-new-V2.xlsx
0
 

Author Comment

by:johnhill2312
ID: 40004374
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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40004378
Apologies about the RC comment earlier, hadn't realised you were using R1C1 style references.

I will take a look at the Conditional Formatting.
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40004390
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
0
 

Author Comment

by:johnhill2312
ID: 40004394
still dont see the changes  Rgonzo1971 - please see the screenshot...

am i missing something?
screen.png
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40004416
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40004426
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
0
 

Author Comment

by:johnhill2312
ID: 40004577
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40004581
Now the CF with no function

=(RC=0)+(inventory!RC=0)

Regards
stock-zero-coz-oos-or-new-V4.xlsx
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40004593
New formula

Taking account if Sales History is zero

=(B2=0)+(inventory!B2=0)+('prod-sales-history'!B2=0)

Regards
stock-zero-coz-oos-or-new-V5.xlsx
0
 

Author Comment

by:johnhill2312
ID: 40006171
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 ??
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40006193
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
0
 

Author Comment

by:johnhill2312
ID: 40006218
Hi Rgonzo
Thanks again, when I followed the steps mentioned above, I am getting an error, attached.
Please advice
screen.png
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40006299
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(InventoryRange,ROW(B2)-2,COLUMN(B2)-2,1,1)=0)

see above to insert it

Regards
stock-zero-coz-oos-or-new-V6.xlsx
0
 

Author Comment

by:johnhill2312
ID: 40007043
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
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40007105
Hi,

In your file the formula you have


=(B2=0)+(OFFSET(ProdRange,ROW(B2)-2,COLUMN(B2)-2,1,1)=0)+(OFFSET(InventoryRange,ROW(B2)-2,COLUMN(B2)-2,1,1)=0)

but instead of B2 you have B3

that's the problem

select all the cells and delete the conditional formatting by deleting the rule

then retry by selecting the cell B2

end enter the formula

and then select the range it will be applied to

Regards
Kopie-von-stock-zero-coz-oos-or-.xlsx
0
 

Author Comment

by:johnhill2312
ID: 40007163
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!!
0
 

Author Closing Comment

by:johnhill2312
ID: 40007164
Many thanks...
0
 

Author Comment

by:johnhill2312
ID: 40018049
hi Rgonzo1971

please can you visit: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28418455.html

I am having a small problem in the formula again...   please can you check?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compile Error 7 41
Hiding column macro 10 28
VBA Array, write each column's start position into an array 17 35
Excel Score Formula 5 49
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now