 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006Format 2 colors
Using Formula =IF(T1399>2000000,"Heavy","Normal"), with conditional formatting I can color Heavy when it pertains, but I cannot color Normal when it applies. How can I have two colors depending on the True or False of the formula? Add a second condition, eg Condition 1: =AND(ISNUMBER(E1),E1>20) Format as desired Condition 2: =AND(E1<=20,E1<>"") Format as desired  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Using Formula =IF(T1399>2000000,"Heavy","Normal"), with conditional > formatting I can color Heavy when it pertains, but I cannot color Normal > when it applies. How can I have two colors depending on the True or False > of the formula? I am trying to format cell color if cell value is greater than a particular number, and format the same cell in a different color if the cell value is less than the particular number. I can use the ISNUMBER function suggested but it seems to require two different entries; i.g. ISNUMBER> in one cell, ISNUMBER< in another cell. Can my objective be met using Conditional Formatting from the Format menu? The particular number is the daily total NYSE Volume. Please comment and advise. Thank you. > Add a second condition, eg > [quoted text clipped - 10 lines] >> False >> of the formula? As per the earlier suggestion, if you want the cell to be formatted in a different color for different values, use another/2nd CF condition. If the earlier suggestion didn't work for you, then the problem could be that the source underlying numbers in the col (your NYSE vol numbers) are text numbers, not real numbers. In which case, we could try coercing the text numbers to real numbers via adding a zero: "+0", ie try instead: Condition 1: =AND(ISNUMBER(E1+0),E1+0>20) Format as desired Condition 2: =AND(ISNUMBER(E1+0),E1+0<=20,E1<>"") Format as desired Adapt the above to suit the column to be formatted (I used col E) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > I am trying to format cell color if cell value is greater than a particular > number, and format the same cell in a different color if the cell value is [quoted text clipped - 3 lines] > Formatting from the Format menu? The particular number is the daily total > NYSE Volume. Please comment and advise. Thank you. Both of your suggested Conditions work (i.e. both with and w/o the '+0' feature. Prehaps I should add: I use the number twenty (20) only as an attempt at brevity - when making entries on the actual worksheet the number will be the greater or less than 2 Billion. Further, if I understand your suggestions, I need to enter condition 1 in a separate cell, e.g. F1; and then enter Condition 2 in another separate cell, e.g. F3. (Is that correct?) I had hoped to use a formula (yours, or "IF") entered in one cell only, and then format using the Conditional Format option on the drop down Format menu. I was able to PARTIALLY achieve this, getting a color into the - say - Greater than cell, but was unable to get a different color into the less than cell when using 'add a second condition.' I was also trying to use NORMAL and HEAVY in place of TRUE & FALSE. Perhaps I ask too much? > As per the earlier suggestion, if you want the cell to be formatted in a > different color for different values, use another/2nd CF condition. [quoted text clipped - 27 lines] >> total >> NYSE Volume. Please comment and advise. Thank you. > .. Further, if I understand your suggestions, I need to enter condition 1 > in a separate cell, e.g. F1; and then enter Condition 2 in another > separate cell, e.g. F3. (Is that correct?) The CF is meant to be applied to the entire col at one go, eg: Select the entire col E (select the col header) Click Format > Conditional Formatting Condition 1, Formula is: =AND(ISNUMBER(E1+0),E1+0>20) Format as desired Click Add (to add condition 2) Condition 2, Formula is: =AND(ISNUMBER(E1+0),E1+0<=20,E1<>"") Format as desired Click OK SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Both of your suggested Conditions work (i.e. both with and w/o the '+0' > feature. Prehaps I should add: I use the number twenty (20) only as an [quoted text clipped - 10 lines] > I was also trying to use NORMAL and HEAVY in place of TRUE & FALSE. > Perhaps I ask too much? > I was also trying to use NORMAL and HEAVY in place of TRUE & FALSE. Assuming you already have an IF formula in col E returning NORMAL and HEAVY, you could try this for the CF Select the entire col E (select the col header) Click Format > Conditional Formatting Condition 1, Formula is: =E1="Heavy" Format as desired Click Add (to add condition 2) Condition 2, Formula is: =E1="Normal" Format as desired Click OK SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik ---
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.