I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is = (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

--
when u change the way u look @ things, the things u look at change.

If you use "Formula is", rather than "Cell value is", then you can use an
OR() condition.
--
David Biddulph

"sahafi" wrote in message
...
I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is = (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but
I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

--
when u change the way u look @ things, the things u look at change.

Its a common misconception that Conditional Formatting is limited to 3
conditions. Actually, it is limited to 3 formats (4 if you count the
default), not conditions. With the use of AND and OR functions, you can have
virtually unlimited conditions.

For your third condition (red), try something like this:

=OR(B1A1+.05,AND(B1<A1-.05,B1.001))

HTH,
Elkar


"sahafi" wrote:

I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is = (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

--
when u change the way u look @ things, the things u look at change.

While your formula worked with the red formatting, the other 2 formatting are
not working as I would like them to. Here's all the formulas:
B4 is the value i'm basing my conditions on.
J7 is the beginning of my range.
#1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working.
#2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'.
#3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'.
I'm only getting 2 format out of it. Either red and green, or red and yellow.
I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it
didn't work either.
#2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001)
#3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001)
I'm not sure if it's the order of the conditions, or I missed something.

Thanks.
--
If u change the way u look @ things, the things u look at change.


"Elkar" wrote:

Its a common misconception that Conditional Formatting is limited to 3
conditions. Actually, it is limited to 3 formats (4 if you count the
default), not conditions. With the use of AND and OR functions, you can have
virtually unlimited conditions.

For your third condition (red), try something like this:

=OR(B1A1+.05,AND(B1<A1-.05,B1.001))

HTH,
Elkar


"sahafi" wrote:

I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is = (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

--
when u change the way u look @ things, the things u look at change.

Your conditional formatting works (I recreated it using the first versions of
#2 and #3 below).

J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than
0.001

J7 is also yellow if it is zero and B4 is between zero and -.05, not
inclusive.

Hope this helps,

Hutch

"sahafi" wrote:

While your formula worked with the red formatting, the other 2 formatting are
not working as I would like them to. Here's all the formulas:
B4 is the value i'm basing my conditions on.
J7 is the beginning of my range.
#1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working.
#2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'.
#3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'.
I'm only getting 2 format out of it. Either red and green, or red and yellow.
I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it
didn't work either.
#2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001)
#3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001)
I'm not sure if it's the order of the conditions, or I missed something.

Thanks.
--
If u change the way u look @ things, the things u look at change.


"Elkar" wrote:

Its a common misconception that Conditional Formatting is limited to 3
conditions. Actually, it is limited to 3 formats (4 if you count the
default), not conditions. With the use of AND and OR functions, you can have
virtually unlimited conditions.

For your third condition (red), try something like this:

=OR(B1A1+.05,AND(B1<A1-.05,B1.001))

HTH,
Elkar


"sahafi" wrote:

I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is = (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

--
when u change the way u look @ things, the things u look at change.

Thanks for the reply. But those are not my conditions.

Basically, if $B$4 is 106% I would like my conditions to be formatted as:

When it's equal to B4 and between B4 and B4 minus 5% then show 'green'.
When it's greater than B4 but less than B4 plus 5% then show 'Yellow'.
When it's greater than (B4+5%) show 'Red'
When it's less than (B4-5%), but greater than zero also show 'Red'.
If it's zero do not format (no background fill).

I hope I made it clearer this time.

Thanks.


-- If u change the way u look @ things, the things u look at change.


"Tom Hutchins" wrote:

Your conditional formatting works (I recreated it using the first versions of
#2 and #3 below).

J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than
0.001

J7 is also yellow if it is zero and B4 is between zero and -.05, not
inclusive.

Hope this helps,

Hutch

"sahafi" wrote:

While your formula worked with the red formatting, the other 2 formatting are
not working as I would like them to. Here's all the formulas:
B4 is the value i'm basing my conditions on.
J7 is the beginning of my range.
#1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working.
#2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'.
#3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'.
I'm only getting 2 format out of it. Either red and green, or red and yellow.
I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it
didn't work either.
#2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001)
#3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001)
I'm not sure if it's the order of the conditions, or I missed something.

Thanks.
--
If u change the way u look @ things, the things u look at change.


"Elkar" wrote:

Its a common misconception that Conditional Formatting is limited to 3
conditions. Actually, it is limited to 3 formats (4 if you count the
default), not conditions. With the use of AND and OR functions, you can have
virtually unlimited conditions.

For your third condition (red), try something like this:

=OR(B1A1+.05,AND(B1<A1-.05,B1.001))

HTH,
Elkar


"sahafi" wrote:

I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is = (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

--
when u change the way u look @ things, the things u look at change.

Try these for your formulas:

Red:
=OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001))

Yellow:
=AND(J7$B$4,J7<=$B$4+0.05)

Green:
=AND(J7<=$B$4,J7=$B$4-0.05)

HTH,
Elkar


"sahafi" wrote:

Thanks for the reply. But those are not my conditions.

Basically, if $B$4 is 106% I would like my conditions to be formatted as:

When it's equal to B4 and between B4 and B4 minus 5% then show 'green'.
When it's greater than B4 but less than B4 plus 5% then show 'Yellow'.
When it's greater than (B4+5%) show 'Red'
When it's less than (B4-5%), but greater than zero also show 'Red'.
If it's zero do not format (no background fill).

I hope I made it clearer this time.

Thanks.


-- If u change the way u look @ things, the things u look at change.


"Tom Hutchins" wrote:

Your conditional formatting works (I recreated it using the first versions of
#2 and #3 below).

J7 is yellow when it equals 0.001 and B4 is greater than -.049 and less than
0.001

J7 is also yellow if it is zero and B4 is between zero and -.05, not
inclusive.

Hope this helps,

Hutch

"sahafi" wrote:

While your formula worked with the red formatting, the other 2 formatting are
not working as I would like them to. Here's all the formulas:
B4 is the value i'm basing my conditions on.
J7 is the beginning of my range.
#1: =OR(J7$B$4+0.05,AND(J7<$B$4-0.05,J70.001)) gives 'Red', it's working.
#2: =OR(J7<=$B$4,AND(J7=$B$4-0.05,J70.001)) supposed to show 'Green'.
#3: =OR(J7$B$4,AND(J7<=$B$4+0.05,J70.001)) supposed to show 'Yellow'.
I'm only getting 2 format out of it. Either red and green, or red and yellow.
I also tried using 'AND' instead of 'OR' on the cond2, and cond3, but it
didn't work either.
#2: =AND(J7=$B$4,J7=$B$4-0.05,J70.001)
#3: =AND(J7$B$4,J7<=$B$4+0.05,J70.001)
I'm not sure if it's the order of the conditions, or I missed something.

Thanks.
--
If u change the way u look @ things, the things u look at change.


"Elkar" wrote:

Its a common misconception that Conditional Formatting is limited to 3
conditions. Actually, it is limited to 3 formats (4 if you count the
default), not conditions. With the use of AND and OR functions, you can have
virtually unlimited conditions.

For your third condition (red), try something like this:

=OR(B1A1+.05,AND(B1<A1-.05,B1.001))

HTH,
Elkar


"sahafi" wrote:

I need to have a conditianal formatting applied to my data as follow:
I would like to compare the condition to a value that changes, say every
month. On $A$1 I have my value 106.2%.

Condition1 when the value is between A1 and A1 + 0.05 = Yellow
Condition2 when the value is between A1 and A1 - 0.05 = Green
Condition3 when the value is = (A1 + 0.05) = Red
Condition4 when the value is between (A1 - 0.05) and 0.001 = Red
Otherwise no format (< 0.0001). Leave zero values without formatting.

I managed to get the first 3 conditions to work but not the last one as
Excel will allow only 3 condition plus the blank one. I have tried to add
additional two cells to hold the value of A1 plus 5% and A1 minus 5%, but I
ended up with the same result. I have also tried the CFPlus Add in, but it
didn't work.
Any help is greatly appreciated.

Thanks.

--
when u change the way u look @ things, the things u look at change.