 |
 | 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 / Worksheet Functions / November 2005CONDITIONAL FORMAT
I need to use three colors with multiple conditions; green = 5.6-6.0 or 6.1-7.0 Yellow= <700 or >900 Red= 1.1-3.8 or 2.0-5.9 or 5.0-6.9 They are in rows; B12 and row C13 and rowD14. I'm ok with the cells, but not formulas. I appreciate you in advance! Thanks, Heat. Select all of B12, C13 and D14 =OR(AND(B12>=5.6,B12<=6),AND(B12>=6.1,B12<=7.0)) =OR(B12<700,B12>900) =OR(AND(B12>=1.1,B12<=3.8),AND(B12>=2.0,B12<=6.9))  SignatureHTH RP (remove nothere from the email address if mailing direct) > I need to use three colors with multiple conditions; > green = 5.6-6.0 or 6.1-7.0 [quoted text clipped - 4 lines] > > Thanks, Heat. Bob, we shouldn't be working on Thanksgiving, however, thanks! It worked, but I needed to replace B with an A otherwise is colored one cell over. Thanks again! HEAT. > Select all of B12, C13 and D14 > [quoted text clipped - 12 lines] > > > > Thanks, Heat. That's okay, we don't do Thanksgiving over here. I don't understand why you had to change B to A from your original post, but I am sure that you do, and that is all that matters. Regards Bob > Bob, we shouldn't be working on Thanksgiving, however, thanks! It worked, but > I needed to replace B with an A otherwise is colored one cell over. Thanks [quoted text clipped - 17 lines] > > > > > > Thanks, Heat. Hey Bob, Here's anotherone for you. This worked great; =OR(AND(B12>=5.6,B12<=6),AND(B12>=6.1,B12<=7.0)) However, I have another request. I want row 26b to 26z to stay clear until I enter a zero in the cell. Then I want the cell to turn green. ONLY WHEN I PUT A "0 (ZERO)" NOT WHEN IT'S CLEAR" Any ideas? Your help is apprecitive. HEAT > That's okay, we don't do Thanksgiving over here. I don't understand why you > had to change B to A from your original post, but I am sure that you do, and [quoted text clipped - 26 lines] > > > > > > > > Thanks, Heat. Try =AND(B26<>"",B26=0) This can be copied to cells C26:Z26 using the format painter. If you want the reference in the condition to change to each cell, be sure to remove the $'s from the formula. I here you on the Thanksgiving thing. Luckily, half a day for me. Cheers, Steve SignatureSteveG HERE'S ANOTHER ONE FOR YOUR PROS. I want the respective condition with each cell in row 31 to change to yellow when it's 26% to 35% of the respective condition with each cell in row 19. If the cell in row 31 is over 36% turn the cell red. i.e. b19 1000 b31 1300 (this should turn yellow) c19 1000 c31 2000 (this should turn red) Basically 1000 * 26%-35%. help me on this please. Thanksgiving a lot!! > Try > [quoted text clipped - 8 lines] > > Steve I presume that you mean over? Select cells in row 31, starting with say column B, CF with formula of =(B31-B$19)/B$19>35% for red and =(B31-B$19)/B$19>26% for yellow SignatureHTH RP (remove nothere from the email address if mailing direct) > HERE'S ANOTHER ONE FOR YOUR PROS. > I want the respective condition with each cell in row 31 to change to yellow [quoted text clipped - 23 lines] > > > > Steve Bob, Thanks for your reply, however, I can't get it to work. The yellow superceeds the red and vice versa, if I take the yellow formual out. HEAT > I presume that you mean over? > [quoted text clipped - 39 lines] > > > View this thread: > http://www.excelforum.com/showthread.php?threadid=487940 >I need to use three colors with multiple conditions; >green = 5.6-6.0 or 6.1-7.0 [quoted text clipped - 4 lines] > >Thanks, Heat. Is 6.1 (for instance) green or red? The conditions seem to overlap. Rgds Richard Buttrey __ If you want to have it color yellow if the % is between 26% and 35% (including 26% & 35%). =AND((B31-B$19)/B$19>=26%,(B31-B$19)/B$19<=35%) The formula for red that Bob gave stays the same. This will cause the cell not to fill with any color if your % falls between 35% and 36%. Cheers, Steve SignatureSteveG SWEET! Thank you for the assistance Steve. UDAMAN! > If you want to have it color yellow if the % is between 26% and 35% > (including 26% & 35%). [quoted text clipped - 7 lines] > > Steve Steve, To spice up things within my conditions I want to add one extra calculation. The formula is great, however, I need it Row 19 to be multiplied by 1000 (onethousand) before row 31 calculates the percentage increase. Any ideas? for instance; =AND((B31-B$19)/B$19*1000>=26%,(B31-B$19)/B$19*1000<=35%) > If you want to have it color yellow if the % is between 26% and 35% > (including 26% & 35%). [quoted text clipped - 7 lines] > > Steve |
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
|
©2009 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.