Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
Using Excel 2003
I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
You don't tell us how you're filling with green. Perhaps you've set the
conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
Thank you for responding. When you select a range of cells and open
conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
That's your problem. A blank cell is the same as 0 in conditional
formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
But sometimes the value will be entered as zero
Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
Maybe you could use:
Formula is: =and(isnumber(a1),a1=0,a1<=4.5) Karen wrote: But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
In CF, use not "Cell Value Is", but "Formula Is":
=AND(A1<"",A1=0,A1<=4.5) -- David Biddulph "Karen" wrote in message ... But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
Thank you for your help - This works fine if I apply this to A1, but if I
change the range in the formula it doesn't work. I must be doing something wrong. Let's say that I'm going to use the cell range A1:A20 to enter the data - This is where I want my conditional formatting to be. I changed the formula to: =AND(A1:A20<"",A1:A20=0,A1:A20<=4.5) What am I doing wrong? Thank, Karen "David Biddulph" wrote: In CF, use not "Cell Value Is", but "Formula Is": =AND(A1<"",A1=0,A1<=4.5) -- David Biddulph "Karen" wrote in message ... But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
Thank you for your help - This works fine if I apply this to A1, but if I
change the range in the formula it doesn't work. I must be doing something wrong. Let's say that I'm going to use the cell range A1:A20 to enter the data - This is where I want my conditional formatting to be. I changed the formula to: =and(isnumber(a1:a20),a1:a20=0,a1:a20<=4.5) What am I doing wrong? Thank, Karen "Dave Peterson" wrote: Maybe you could use: Formula is: =and(isnumber(a1),a1=0,a1<=4.5) Karen wrote: But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
I think that what you're doing wrong is changing the formula.
I assumed that you wanted each cell to be formatted based on its own content. If that is so, with relative addressing the CF formula will do the job as it is. Either: 1 select your range A1:A20 and put in the conditional formatting as I suggested, or 2 apply your CF to A1 and use format painter to apply it to A2:A20, or 3 apply your CF to A1, then copy, and use Edit/ Paste Special/ Formats, to apply to A2:A20 In any of those cases, the CF formula in A1 will refer to cell A1, and then automatically the CF formula in A2 will refer to A2, and so on. -- David Biddulph "Karen" wrote in message ... Thank you for your help - This works fine if I apply this to A1, but if I change the range in the formula it doesn't work. I must be doing something wrong. Let's say that I'm going to use the cell range A1:A20 to enter the data - This is where I want my conditional formatting to be. I changed the formula to: =AND(A1:A20<"",A1:A20=0,A1:A20<=4.5) What am I doing wrong? Thank, Karen "David Biddulph" wrote: In CF, use not "Cell Value Is", but "Formula Is": =AND(A1<"",A1=0,A1<=4.5) -- David Biddulph "Karen" wrote in message ... But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
Thank you for your help - I followed your instructions and when I enter any
number from 0 to 4.5, the cell background is white and I have it set for green. I must be doing something wrong but I cannor figure out what it is? I am using "Formula Is" Karen "David Biddulph" wrote: I think that what you're doing wrong is changing the formula. I assumed that you wanted each cell to be formatted based on its own content. If that is so, with relative addressing the CF formula will do the job as it is. Either: 1 select your range A1:A20 and put in the conditional formatting as I suggested, or 2 apply your CF to A1 and use format painter to apply it to A2:A20, or 3 apply your CF to A1, then copy, and use Edit/ Paste Special/ Formats, to apply to A2:A20 In any of those cases, the CF formula in A1 will refer to cell A1, and then automatically the CF formula in A2 will refer to A2, and so on. -- David Biddulph "Karen" wrote in message ... Thank you for your help - This works fine if I apply this to A1, but if I change the range in the formula it doesn't work. I must be doing something wrong. Let's say that I'm going to use the cell range A1:A20 to enter the data - This is where I want my conditional formatting to be. I changed the formula to: =AND(A1:A20<"",A1:A20=0,A1:A20<=4.5) What am I doing wrong? Thank, Karen "David Biddulph" wrote: In CF, use not "Cell Value Is", but "Formula Is": =AND(A1<"",A1=0,A1<=4.5) -- David Biddulph "Karen" wrote in message ... But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
Thank you for your help - I followed your instructions and when I enter any
number from 0 to 4.5, the cell background is white and I have it set for green. I must be doing something wrong but I cannor figure out what it is? I am using "Formula Is" Karen "David Biddulph" wrote: In CF, use not "Cell Value Is", but "Formula Is": =AND(A1<"",A1=0,A1<=4.5) -- David Biddulph "Karen" wrote in message ... But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formmating
For one of the cells where you aren't getting the result you expect, use
copy and paste to copy the CF formula from where you are using "Formula Is" in CF to here in the newsgroup message, and please also tell us what value you have in the cell. [And make sure that the cell reference in the formula is the cell reference of the cell from which you are copying the formula.] While you are at it, also copy that CF formula to the formula bar in a spare cell. If that cell into which you copy the now shows TRUE, then you should be invoking the conditional formatting colour scheme in the cell from which you have copied the formula. If that cell shows FALSE, then it shouldn't be invoking the CF colour scheme. -- David Biddulph "Karen" wrote in message ... Thank you for your help - I followed your instructions and when I enter any number from 0 to 4.5, the cell background is white and I have it set for green. I must be doing something wrong but I cannor figure out what it is? I am using "Formula Is" Karen "David Biddulph" wrote: I think that what you're doing wrong is changing the formula. I assumed that you wanted each cell to be formatted based on its own content. If that is so, with relative addressing the CF formula will do the job as it is. Either: 1 select your range A1:A20 and put in the conditional formatting as I suggested, or 2 apply your CF to A1 and use format painter to apply it to A2:A20, or 3 apply your CF to A1, then copy, and use Edit/ Paste Special/ Formats, to apply to A2:A20 In any of those cases, the CF formula in A1 will refer to cell A1, and then automatically the CF formula in A2 will refer to A2, and so on. -- David Biddulph "Karen" wrote in message ... Thank you for your help - This works fine if I apply this to A1, but if I change the range in the formula it doesn't work. I must be doing something wrong. Let's say that I'm going to use the cell range A1:A20 to enter the data - This is where I want my conditional formatting to be. I changed the formula to: =AND(A1:A20<"",A1:A20=0,A1:A20<=4.5) What am I doing wrong? Thank, Karen "David Biddulph" wrote: In CF, use not "Cell Value Is", but "Formula Is": =AND(A1<"",A1=0,A1<=4.5) -- David Biddulph "Karen" wrote in message ... But sometimes the value will be entered as zero Karen "Tyro" wrote: That's your problem. A blank cell is the same as 0 in conditional formatting. Change the condition to greater than 0 and less equal 4.5. Tyro "Karen" wrote in message ... Thank you for responding. When you select a range of cells and open conditional formatting and set the condition to "Cell Value is" - "Between" - I entered the minimum value as 0 and the maximum value as 4.5. Now before I even enter a numerical value in any one of those cells, that has conditional formatting applied, the background is green. Hope this helps - Thanks, Karen "Tyro" wrote: You don't tell us how you're filling with green. Perhaps you've set the conditional formatting to fill with green if nothing is entered. Tyro "Karen" wrote in message ... Using Excel 2003 I have a range of cells from A1 to A20 that has conditional formmating. If someone enters a number from zero to 4.5, the background turns green. The problem is, before someone even enters a number, the background is green. Why is this? I don't want to confuse the data entry person. I have 2 other conditions in those cells that turn the cells yellow and red when other values are entered. How can I get rid of the green background? Thank you, Karen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
Conditional Sum | Excel Worksheet Functions | |||
conditional formmating data and put it into a graph... | Charts and Charting in Excel | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |