 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008Conditional 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 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 > Using Excel 2003 > I have a range of cells from A1 to A20 that has conditional formmating. If [quoted text clipped - 5 lines] > values are entered. How can I get rid of the green background? > Thank you, Karen 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 > 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. [quoted text clipped - 10 lines] > > values are entered. How can I get rid of the green background? > > Thank you, Karen 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 > Thank you for responding. When you select a range of cells and open > conditional formatting and set the condition to "Cell Value is" - [quoted text clipped - 23 lines] >> > values are entered. How can I get rid of the green background? >> > Thank you, Karen But sometimes the value will be entered as zero Karen > 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. [quoted text clipped - 28 lines] > >> > values are entered. How can I get rid of the green background? > >> > Thank you, Karen Maybe you could use: Formula is: =and(isnumber(a1),a1>=0,a1<=4.5) > But sometimes the value will be entered as zero > Karen [quoted text clipped - 31 lines] > > >> > values are entered. How can I get rid of the green background? > > >> > Thank you, Karen  SignatureDave Peterson 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 > Maybe you could use: > Formula is: [quoted text clipped - 35 lines] > > > >> > values are entered. How can I get rid of the green background? > > > >> > Thank you, Karen In CF, use not "Cell Value Is", but "Formula Is": =AND(A1<>"",A1>=0,A1<=4.5) SignatureDavid Biddulph > But sometimes the value will be entered as zero > Karen [quoted text clipped - 37 lines] >> >> > values are entered. How can I get rid of the green background? >> >> > Thank you, Karen 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 > In CF, use not "Cell Value Is", but "Formula Is": > =AND(A1<>"",A1>=0,A1<=4.5) [quoted text clipped - 39 lines] > >> >> > values are entered. How can I get rid of the green background? > >> >> > Thank you, Karen 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 > 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 [quoted text clipped - 49 lines] >> >> >> > values are entered. How can I get rid of the green background? >> >> >> > Thank you, Karen 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 > I think that what you're doing wrong is changing the formula. > [quoted text clipped - 66 lines] > >> >> >> > values are entered. How can I get rid of the green background? > >> >> >> > Thank you, Karen 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. SignatureDavid Biddulph > Thank you for your help - I followed your instructions and when I enter > any [quoted text clipped - 88 lines] >> >> >> >> > values are entered. How can I get rid of the green background? >> >> >> >> > Thank you, Karen 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 > In CF, use not "Cell Value Is", but "Formula Is": > =AND(A1<>"",A1>=0,A1<=4.5) [quoted text clipped - 39 lines] > >> >> > values are entered. How can I get rid of the green background? > >> >> > Thank you, Karen |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.