Conditional Formmating

[Karen]

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

 

[Tyro]

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]

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]

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]

But sometimes the value will be entered as zero
Karen

 

[Dave Peterson]

Maybe you could use:
Formula is:
=and(isnumber(a1),a1>=0,a1<=4.5)

 

[David Biddulph]

In CF, use not "Cell Value Is", but "Formula Is":
=AND(A1<>"",A1>=0,A1<=4.5)

 

[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

 

[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(isnumber(a1:a20),a1:a20>=0,a1:a20<=4.5)
What am I doing wrong?
Thank, Karen

 

[David Biddulph]

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.

 

[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

 

[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

 

[David Biddulph]

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.