Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

I changed that slightly as I have a block of cells that needs the format so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

Dave,
Highlight the cells to which you want to apply the CF and then
enter the CF conditions.

In my testing, I had your formula =IF(A1="","",B1-C1) in column D and copied
down for say 10 cells. I highlighted column D and set the CF conditions and
it worked OK.
All postive results were green, negative red and where A1 was blank, the
cell was blank.

I copied the formula to column E and it worked except for change below (as
copying changed reference to B1):

1st condition should be =ISBLANK($A1)

HTH

"DaveyJones" wrote:

I changed that slightly as I have a block of cells that needs the format so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

Genius, sorry, me being thick. So don't use the current cell, use the cell
your checking if it's blank to determine the format. OK, thanks alot. It's
one of those why didn't i think of that situations.


--
Dave


"Toppers" wrote:

Dave,
Highlight the cells to which you want to apply the CF and then
enter the CF conditions.

In my testing, I had your formula =IF(A1="","",B1-C1) in column D and copied
down for say 10 cells. I highlighted column D and set the CF conditions and
it worked OK.
All postive results were green, negative red and where A1 was blank, the
cell was blank.

I copied the formula to column E and it worked except for change below (as
copying changed reference to B1):

1st condition should be =ISBLANK($A1)

HTH

"DaveyJones" wrote:

I changed that slightly as I have a block of cells that needs the format so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

Surely, that is because your formula returns the cell address not the cell
content.

Try =ISBLANK(A1) or wherever you put the CF.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DaveyJones" wrote in message
...
I changed that slightly as I have a block of cells that needs the format
so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green
if it
is above 0, red if it is below and no colour if it is 0, however, the
values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason
the
condional format see's the formula as being greater than 0 and
whatever I do,
it colours the cell green, any suggestions??
--
Dave

Actually, I just gave it another go and for some reason it still is not
giving me no format for a blank cell. I've used the =ISBLANK($D20) code and
had to manually change it even though i copied it down because the
conditional format didn't auto change the cell reference. I've tried what
you've suggest three times, but it still will not give me a blank cell
format. Cell D20 is blank(no formula or anything) and the exact formula in
cell f20 is =IF(D20="","",C20-(D20-D19)). F20 is where the conditional format
is. I don't understand it.


--
Dave


"Toppers" wrote:

Dave,
Highlight the cells to which you want to apply the CF and then
enter the CF conditions.

In my testing, I had your formula =IF(A1="","",B1-C1) in column D and copied
down for say 10 cells. I highlighted column D and set the CF conditions and
it worked OK.
All postive results were green, negative red and where A1 was blank, the
cell was blank.

I copied the formula to column E and it worked except for change below (as
copying changed reference to B1):

1st condition should be =ISBLANK($A1)

HTH

"DaveyJones" wrote:

I changed that slightly as I have a block of cells that needs the format so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

Highlight column F as I assume all the formulae are in this column:

Set 1st condition to Formula is: =ISBLANK($D1)

Set other conditions as previously described

The CF is "automatically" applied to each cell i.e. if you highlight F20 and
go to CF you see the 1st condition is =ISBLANK($D20)

This works ...I've just tried it with your lastest formula and with D20
blank I get a blank cell.

HTH (again!)

"DaveyJones" wrote:

Actually, I just gave it another go and for some reason it still is not
giving me no format for a blank cell. I've used the =ISBLANK($D20) code and
had to manually change it even though i copied it down because the
conditional format didn't auto change the cell reference. I've tried what
you've suggest three times, but it still will not give me a blank cell
format. Cell D20 is blank(no formula or anything) and the exact formula in
cell f20 is =IF(D20="","",C20-(D20-D19)). F20 is where the conditional format
is. I don't understand it.


--
Dave


"Toppers" wrote:

Dave,
Highlight the cells to which you want to apply the CF and then
enter the CF conditions.

In my testing, I had your formula =IF(A1="","",B1-C1) in column D and copied
down for say 10 cells. I highlighted column D and set the CF conditions and
it worked OK.
All postive results were green, negative red and where A1 was blank, the
cell was blank.

I copied the formula to column E and it worked except for change below (as
copying changed reference to B1):

1st condition should be =ISBLANK($A1)

HTH

"DaveyJones" wrote:

I changed that slightly as I have a block of cells that needs the format so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

I have done exactly what you said. And it still left the cell as green. I've
checked all the cells involved and they are all in the state I expect(Blank).
I don't know what the problem is on my spreadsheet but I have found a way
around it. I've used the cells value is between -100000 and 100000 as non of
my values are above that. Thanks very much for your continued help.
--
Dave


"Toppers" wrote:

Highlight column F as I assume all the formulae are in this column:

Set 1st condition to Formula is: =ISBLANK($D1)

Set other conditions as previously described

The CF is "automatically" applied to each cell i.e. if you highlight F20 and
go to CF you see the 1st condition is =ISBLANK($D20)

This works ...I've just tried it with your lastest formula and with D20
blank I get a blank cell.

HTH (again!)

"DaveyJones" wrote:

Actually, I just gave it another go and for some reason it still is not
giving me no format for a blank cell. I've used the =ISBLANK($D20) code and
had to manually change it even though i copied it down because the
conditional format didn't auto change the cell reference. I've tried what
you've suggest three times, but it still will not give me a blank cell
format. Cell D20 is blank(no formula or anything) and the exact formula in
cell f20 is =IF(D20="","",C20-(D20-D19)). F20 is where the conditional format
is. I don't understand it.


--
Dave


"Toppers" wrote:

Dave,
Highlight the cells to which you want to apply the CF and then
enter the CF conditions.

In my testing, I had your formula =IF(A1="","",B1-C1) in column D and copied
down for say 10 cells. I highlighted column D and set the CF conditions and
it worked OK.
All postive results were green, negative red and where A1 was blank, the
cell was blank.

I copied the formula to column E and it worked except for change below (as
copying changed reference to B1):

1st condition should be =ISBLANK($A1)

HTH

"DaveyJones" wrote:

I changed that slightly as I have a block of cells that needs the format so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave

If you want me to look at your s/sheet, send to:

toppers<atjohntopley.fsnet.co.uk

but if you are OK with your solution that's fine.

"DaveyJones" wrote:

I have done exactly what you said. And it still left the cell as green. I've
checked all the cells involved and they are all in the state I expect(Blank).
I don't know what the problem is on my spreadsheet but I have found a way
around it. I've used the cells value is between -100000 and 100000 as non of
my values are above that. Thanks very much for your continued help.
--
Dave


"Toppers" wrote:

Highlight column F as I assume all the formulae are in this column:

Set 1st condition to Formula is: =ISBLANK($D1)

Set other conditions as previously described

The CF is "automatically" applied to each cell i.e. if you highlight F20 and
go to CF you see the 1st condition is =ISBLANK($D20)

This works ...I've just tried it with your lastest formula and with D20
blank I get a blank cell.

HTH (again!)

"DaveyJones" wrote:

Actually, I just gave it another go and for some reason it still is not
giving me no format for a blank cell. I've used the =ISBLANK($D20) code and
had to manually change it even though i copied it down because the
conditional format didn't auto change the cell reference. I've tried what
you've suggest three times, but it still will not give me a blank cell
format. Cell D20 is blank(no formula or anything) and the exact formula in
cell f20 is =IF(D20="","",C20-(D20-D19)). F20 is where the conditional format
is. I don't understand it.


--
Dave


"Toppers" wrote:

Dave,
Highlight the cells to which you want to apply the CF and then
enter the CF conditions.

In my testing, I had your formula =IF(A1="","",B1-C1) in column D and copied
down for say 10 cells. I highlighted column D and set the CF conditions and
it worked OK.
All postive results were green, negative red and where A1 was blank, the
cell was blank.

I copied the formula to column E and it worked except for change below (as
copying changed reference to B1):

1st condition should be =ISBLANK($A1)

HTH

"DaveyJones" wrote:

I changed that slightly as I have a block of cells that needs the format so I
put

=ISBLANK(ADDRESS(ROWS(),COLUMN())) but it still keeps the cell highlighted
green. Even using the ISBLANK formula it still does not change the format.
I'm stumped by this...


--
Dave


"Toppers" wrote:

Dave,

Try:

1st condition Formula is: =ISBLANK(A1) ... no format set

2nd condition: Cell Value is: Greater than 0 (green)

3rd Condition: Cell value is: Less than 0 (red)

HTH

"DaveyJones" wrote:

Probably quite an easy one but i'm trying to get the cell to be green if it
is above 0, red if it is below and no colour if it is 0, however, the values
in the cells are part of a formula ie

If(A1="", "", B1-C2)

so if A1 is blank, the current cell will be blank. But for some reason the
condional format see's the formula as being greater than 0 and whatever I do,
it colours the cell green, any suggestions??
--
Dave