I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

Hi,

Hi,

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=AND(A1<0.25,A1<0.5)
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=AND(A1<0.25,A1<0.5)
5. Click the Format button and choose a format.
6. Click OK twice

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

Hi

Use this as the CF formula

=AND(A1<0.25,A1<0.5)

Mike

"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

You might also consider using Data Validation.
For example, assume the cell you have for this value is A4, go to menu
Data--Validation
Choose Custom under the Allow pull down
Then in criteria type the following formula:
=OR(A4=0.5,A4=0.25)

Note: If you have a range of cells for this, it is just as easy, select your
range, and type the above formula, substituting whichever cell is the
'active' cell in the selected range. (It will be the one that is not shaded
but is in the range). So long as you don't anchor the cell reference with $,
then it will change itself as needed.

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

thanks Mike and Shane,
it works.
but sorry i missed this when i asked the question.

I also want the cells to have no formatting when blank (empty)


"Mike H" wrote:

Hi

Use this as the CF formula

=AND(A1<0.25,A1<0.5)

Mike

"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

thanks John, thats what i wanted actually (data validation)


"John C" wrote:

You might also consider using Data Validation.
For example, assume the cell you have for this value is A4, go to menu
Data--Validation
Choose Custom under the Allow pull down
Then in criteria type the following formula:
=OR(A4=0.5,A4=0.25)

Note: If you have a range of cells for this, it is just as easy, select your
range, and type the above formula, substituting whichever cell is the
'active' cell in the selected range. (It will be the one that is not shaded
but is in the range). So long as you don't anchor the cell reference with $,
then it will change itself as needed.

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

thanks John,
your formulla is working using data validation,
but how do I do this for selecting many cells.

i tried usiing this =OR(A4:B12=0.5,A4:B12=0.25)
but it still works in only one cell.


"John C" wrote:

You might also consider using Data Validation.
For example, assume the cell you have for this value is A4, go to menu
Data--Validation
Choose Custom under the Allow pull down
Then in criteria type the following formula:
=OR(A4=0.5,A4=0.25)

Note: If you have a range of cells for this, it is just as easy, select your
range, and type the above formula, substituting whichever cell is the
'active' cell in the selected range. (It will be the one that is not shaded
but is in the range). So long as you don't anchor the cell reference with $,
then it will change itself as needed.

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

I am assuming that all cells in range A4:B12 (i.e.: A4 to A12 and B4 to B12)
you want to have the data validation for?
If that is the case, click in cell A4, then highlight your entire range
(down to B12). If you'll notice, the cell A4 is still white, while the other
cells all have a shading to them.

Go to Data Validation, and type my formula exactly as indicated:
=OR(A4=0.5,A4=0.25)
and then choose your formatting

Since we are not anchoring the cells (i.e.: $A$4), the formula will
automatically adjust itself for all the other cells.
--
** John C **

"Raz" wrote:

thanks John,
your formulla is working using data validation,
but how do I do this for selecting many cells.

i tried usiing this =OR(A4:B12=0.5,A4:B12=0.25)
but it still works in only one cell.


"John C" wrote:

You might also consider using Data Validation.
For example, assume the cell you have for this value is A4, go to menu
Data--Validation
Choose Custom under the Allow pull down
Then in criteria type the following formula:
=OR(A4=0.5,A4=0.25)

Note: If you have a range of cells for this, it is just as easy, select your
range, and type the above formula, substituting whichever cell is the
'active' cell in the selected range. (It will be the one that is not shaded
but is in the range). So long as you don't anchor the cell reference with $,
then it will change itself as needed.

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell color
should turn red.

Select your range A4:B12 and make sure that cell A4 is the active cell
(reference shown in the name box to the left of the formula bar). [Do this
by selecting A4 then shift B12, rather than B12 and then shift A4.]
Use the formula =OR(A4=0.5,A4=0.25)
If you then look at B7, for example, you will see that the A4s in the DV
formula will have been updated to B7s.
--
David Biddulph

"Raz" wrote in message
...
thanks John,
your formulla is working using data validation,
but how do I do this for selecting many cells.

i tried usiing this =OR(A4:B12=0.5,A4:B12=0.25)
but it still works in only one cell.


"John C" wrote:

You might also consider using Data Validation.
For example, assume the cell you have for this value is A4, go to menu
Data--Validation
Choose Custom under the Allow pull down
Then in criteria type the following formula:
=OR(A4=0.5,A4=0.25)

Note: If you have a range of cells for this, it is just as easy, select
your
range, and type the above formula, substituting whichever cell is the
'active' cell in the selected range. (It will be the one that is not
shaded
but is in the range). So long as you don't anchor the cell reference with
$,
then it will change itself as needed.

--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Raz" wrote:

I would like to have a conditional formulla, so that only .25 or .5
(Numbers) can be typed in a cell. If anything else is typed the cell
color
should turn red.