Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting in Excel
Hi,
If D1 is empty then G1 should be white(clear) if D1 has a number in it then G1 should be red additionally; if a value, either a figure 0 or a currency value is entered in G1, which owing to the previous two conditions, would be red, then G1 should change to blue. If anyone can explain how to acheive this I would be extremely grateful. Thank You Brian |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting in Excel
Ok, you have a few conditions:
Isblank(d1) means clear Formatted as Currency Means €œBlue€ Any value not formatted as Currency Means Red Step 1 I would insert a column to calculate this and format based on the results of the formula: =IF(ISBLANK(D1),"clear",IF(AND(CELL("format",D1)= "C",CELL("format",D1)<="C2"),"blue",IF(D1=0,"Blue" ,"Red"))) Then Step two, Use conditional Formatting. You would need to set 3 conditions (leaving the result of clear to do nothing). Assuming you insert the above formula into column E the First formula in conditional formatting would look like this: =$E1="Red" Set the result of the condition to a Red fill color. Note: Make sure to remove the €œ$€ in front of the 1 otherwise it will not work if these conditions are applisd to more than one row. Just do the same for €œBlue€ =$E1="Blue" Hope that helps! Frank "Brian" wrote: Hi, If D1 is empty then G1 should be white(clear) if D1 has a number in it then G1 should be red additionally; if a value, either a figure 0 or a currency value is entered in G1, which owing to the previous two conditions, would be red, then G1 should change to blue. If anyone can explain how to acheive this I would be extremely grateful. Thank You Brian |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting in Excel
Frank
Many thanks for your effort - it almost works for me, but I probably didn't clearly explain. the first column below is "D" the next is irelevant for this question, the next is "G" "G" would initially be blank and therefore RED if there is a number in that row in "D" At some stage I would enter either a figure 0 (zero) or an actual currency valueat which point the cell in "G" should change to BLUE ie it is a dynamic rather than a static worksheet. I have a rudimentary knowledge of the formulas but some parts of your example are a bit beyond me such as the expression "IF(AND(CELL("format",D1)="C"," If you could throw any further light on it for me I would be really 'chuffed' Brian 1 1b 21.00 2 2b 3 1a 4 2a 5 2½ 2.00 6 5a 4.00 7 10a 4.00 8 9 10 2c on 1b 10.50 11 5c on 2b 10.50 12 10c on 1a 10.50 13 15c on 2a 16.00 14 20c on 2a 16.00 15 25c on 2½ 16 1l on 10a "FrankWood" wrote: Ok, you have a few conditions: Isblank(d1) means clear Formatted as Currency Means €œBlue€ Any value not formatted as Currency Means Red Step 1 I would insert a column to calculate this and format based on the results of the formula: =IF(ISBLANK(D1),"clear",IF(AND(CELL("format",D1)= "C",CELL("format",D1)<="C2"),"blue",IF(D1=0,"Blue" ,"Red"))) Then Step two, Use conditional Formatting. You would need to set 3 conditions (leaving the result of clear to do nothing). Assuming you insert the above formula into column E the First formula in conditional formatting would look like this: =$E1="Red" Set the result of the condition to a Red fill color. Note: Make sure to remove the €œ$€ in front of the 1 otherwise it will not work if these conditions are applisd to more than one row. Just do the same for €œBlue€ =$E1="Blue" Hope that helps! Frank "Brian" wrote: Hi, If D1 is empty then G1 should be white(clear) if D1 has a number in it then G1 should be red additionally; if a value, either a figure 0 or a currency value is entered in G1, which owing to the previous two conditions, would be red, then G1 should change to blue. If anyone can explain how to acheive this I would be extremely grateful. Thank You Brian |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting in Excel
Frank
I have managed to work it out by examining your code more closely - it now does exactly what I wanted - I am really pleased. Thank you very much indeed for your help Brian "Brian" wrote: Frank Many thanks for your effort - it almost works for me, but I probably didn't clearly explain. the first column below is "D" the next is irelevant for this question, the next is "G" "G" would initially be blank and therefore RED if there is a number in that row in "D" At some stage I would enter either a figure 0 (zero) or an actual currency valueat which point the cell in "G" should change to BLUE ie it is a dynamic rather than a static worksheet. I have a rudimentary knowledge of the formulas but some parts of your example are a bit beyond me such as the expression "IF(AND(CELL("format",D1)="C"," If you could throw any further light on it for me I would be really 'chuffed' Brian 1 1b 21.00 2 2b 3 1a 4 2a 5 2½ 2.00 6 5a 4.00 7 10a 4.00 8 9 10 2c on 1b 10.50 11 5c on 2b 10.50 12 10c on 1a 10.50 13 15c on 2a 16.00 14 20c on 2a 16.00 15 25c on 2½ 16 1l on 10a "FrankWood" wrote: Ok, you have a few conditions: Isblank(d1) means clear Formatted as Currency Means €œBlue€ Any value not formatted as Currency Means Red Step 1 I would insert a column to calculate this and format based on the results of the formula: =IF(ISBLANK(D1),"clear",IF(AND(CELL("format",D1)= "C",CELL("format",D1)<="C2"),"blue",IF(D1=0,"Blue" ,"Red"))) Then Step two, Use conditional Formatting. You would need to set 3 conditions (leaving the result of clear to do nothing). Assuming you insert the above formula into column E the First formula in conditional formatting would look like this: =$E1="Red" Set the result of the condition to a Red fill color. Note: Make sure to remove the €œ$€ in front of the 1 otherwise it will not work if these conditions are applisd to more than one row. Just do the same for €œBlue€ =$E1="Blue" Hope that helps! Frank "Brian" wrote: Hi, If D1 is empty then G1 should be white(clear) if D1 has a number in it then G1 should be red additionally; if a value, either a figure 0 or a currency value is entered in G1, which owing to the previous two conditions, would be red, then G1 should change to blue. If anyone can explain how to acheive this I would be extremely grateful. Thank You Brian |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting in Excel
Glad to help. I guess I forgot to tell you to set the conditional formatting
in Column G and base the conditional formatting on the formula where ever you place it. Happy it helped. Frank "Brian" wrote: Frank I have managed to work it out by examining your code more closely - it now does exactly what I wanted - I am really pleased. Thank you very much indeed for your help Brian "Brian" wrote: Frank Many thanks for your effort - it almost works for me, but I probably didn't clearly explain. the first column below is "D" the next is irelevant for this question, the next is "G" "G" would initially be blank and therefore RED if there is a number in that row in "D" At some stage I would enter either a figure 0 (zero) or an actual currency valueat which point the cell in "G" should change to BLUE ie it is a dynamic rather than a static worksheet. I have a rudimentary knowledge of the formulas but some parts of your example are a bit beyond me such as the expression "IF(AND(CELL("format",D1)="C"," If you could throw any further light on it for me I would be really 'chuffed' Brian 1 1b 21.00 2 2b 3 1a 4 2a 5 2½ 2.00 6 5a 4.00 7 10a 4.00 8 9 10 2c on 1b 10.50 11 5c on 2b 10.50 12 10c on 1a 10.50 13 15c on 2a 16.00 14 20c on 2a 16.00 15 25c on 2½ 16 1l on 10a "FrankWood" wrote: Ok, you have a few conditions: Isblank(d1) means clear Formatted as Currency Means €œBlue€ Any value not formatted as Currency Means Red Step 1 I would insert a column to calculate this and format based on the results of the formula: =IF(ISBLANK(D1),"clear",IF(AND(CELL("format",D1)= "C",CELL("format",D1)<="C2"),"blue",IF(D1=0,"Blue" ,"Red"))) Then Step two, Use conditional Formatting. You would need to set 3 conditions (leaving the result of clear to do nothing). Assuming you insert the above formula into column E the First formula in conditional formatting would look like this: =$E1="Red" Set the result of the condition to a Red fill color. Note: Make sure to remove the €œ$€ in front of the 1 otherwise it will not work if these conditions are applisd to more than one row. Just do the same for €œBlue€ =$E1="Blue" Hope that helps! Frank "Brian" wrote: Hi, If D1 is empty then G1 should be white(clear) if D1 has a number in it then G1 should be red additionally; if a value, either a figure 0 or a currency value is entered in G1, which owing to the previous two conditions, would be red, then G1 should change to blue. If anyone can explain how to acheive this I would be extremely grateful. Thank You Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Conditional Formatting | Excel Discussion (Misc queries) | |||
Excel 07 Conditional Formatting Help! | Excel Worksheet Functions | |||
Conditional formatting in Excel | New Users to Excel | |||
conditional formatting in excel | Excel Worksheet Functions | |||
Conditional Formatting in Excel Help Please..... | Excel Discussion (Misc queries) |