Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
Select all of B12, C13 and D14
=OR(AND(B12=5.6,B12<=6),AND(B12=6.1,B12<=7.0)) =OR(B12<700,B12900) =OR(AND(B12=1.1,B12<=3.8),AND(B12=2.0,B12<=6.9)) -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
On Thu, 24 Nov 2005 06:20:07 -0800, "John"
wrote: 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. Is 6.1 (for instance) green or red? The conditions seem to overlap. Rgds Richard Buttrey __ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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. "Bob Phillips" wrote: Select all of B12, C13 and D14 =OR(AND(B12=5.6,B12<=6),AND(B12=6.1,B12<=7.0)) =OR(B12<700,B12900) =OR(AND(B12=1.1,B12<=3.8),AND(B12=2.0,B12<=6.9)) -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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 "John" wrote in message ... 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. "Bob Phillips" wrote: Select all of B12, C13 and D14 =OR(AND(B12=5.6,B12<=6),AND(B12=6.1,B12<=7.0)) =OR(B12<700,B12900) =OR(AND(B12=1.1,B12<=3.8),AND(B12=2.0,B12<=6.9)) -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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 "Bob Phillips" wrote: 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 "John" wrote in message ... 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. "Bob Phillips" wrote: Select all of B12, C13 and D14 =OR(AND(B12=5.6,B12<=6),AND(B12=6.1,B12<=7.0)) =OR(B12<700,B12900) =OR(AND(B12=1.1,B12<=3.8),AND(B12=2.0,B12<=6.9)) -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487940 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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!! "SteveG" wrote: 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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487940 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
I presume that you mean over?
Select cells in row 31, starting with say column B, CF with formula of =(B31-B$19)/B$1935% for red and =(B31-B$19)/B$1926% for yellow -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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!! "SteveG" wrote: 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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487940 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487940 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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 "Bob Phillips" wrote: I presume that you mean over? Select cells in row 31, starting with say column B, CF with formula of =(B31-B$19)/B$1935% for red and =(B31-B$19)/B$1926% for yellow -- HTH RP (remove nothere from the email address if mailing direct) "John" wrote in message ... 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!! "SteveG" wrote: 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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487940 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
SWEET! Thank you for the assistance Steve. UDAMAN!
"SteveG" wrote: 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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487940 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CONDITIONAL FORMAT
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%) "SteveG" wrote: 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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=487940 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation or Conditional Format (or combo of) | Excel Worksheet Functions | |||
Conditional format numbers | Excel Worksheet Functions | |||
Cell Format Changes When Data Is Entered - Not Conditional Formatt | Excel Worksheet Functions | |||
Conditional format of minimum number | Excel Worksheet Functions | |||
Conditional Format Question | Excel Worksheet Functions |