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

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

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

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

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