Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Format
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
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 Question | Excel Worksheet Functions |