I want to use data validation and conditional formatting together, so that...

1) the user is limited to entering a number = 0
2) if the value is 0 the cell is red
3) if the value is 0 the cell is green
4) if the value is nothing (blank) the cell stays the same.

I'm using Excel 2000, and can't seem to get the combination of these 2
things to give me the results I want. Any ideas?

Thanks.

Hi Shelly

1. Say it is A1:A100 you want to format this way, select A1:A100.
2. Go DataValidation and change to Custom and use:

=A1=0

and making sure "Ignore blanks" is checked click OK

3. Go FormatConditional Formatting and change to Formula Is
4. Type in

=AND(A1<"",A1=0)

and select format of red pattern
5. Add condition and go Formula Is and use:

=A10

and select format of green pattern.
6. Click OK. Your conditionaql formatting/validation should now be set!

Hope this helps!

Richard

Shelly wrote:
I want to use data validation and conditional formatting together, so that...

1) the user is limited to entering a number = 0
2) if the value is 0 the cell is red
3) if the value is 0 the cell is green
4) if the value is nothing (blank) the cell stays the same.

I'm using Excel 2000, and can't seem to get the combination of these 2
things to give me the results I want. Any ideas?

Thanks.

EXCELLENT! PERFECT! Thank you!!

"RichardSchollar" wrote:

Hi Shelly

1. Say it is A1:A100 you want to format this way, select A1:A100.
2. Go DataValidation and change to Custom and use:

=A1=0

and making sure "Ignore blanks" is checked click OK

3. Go FormatConditional Formatting and change to Formula Is
4. Type in

=AND(A1<"",A1=0)

and select format of red pattern
5. Add condition and go Formula Is and use:

=A10

and select format of green pattern.
6. Click OK. Your conditionaql formatting/validation should now be set!

Hope this helps!

Richard

Shelly wrote:
I want to use data validation and conditional formatting together, so that...

1) the user is limited to entering a number = 0
2) if the value is 0 the cell is red
3) if the value is 0 the cell is green
4) if the value is nothing (blank) the cell stays the same.

I'm using Excel 2000, and can't seem to get the combination of these 2
things to give me the results I want. Any ideas?

Thanks.

Select your range
Conditonal Formatting
Conditon 1
Cell Value Is next tab select equal to next tab enter 0
format cell as red

Condition 2
Cell Value Is next tab select greater than next tab enter 0
format cell as green

"Shelly" wrote:

I want to use data validation and conditional formatting together, so that...

1) the user is limited to entering a number = 0
2) if the value is 0 the cell is red
3) if the value is 0 the cell is green
4) if the value is nothing (blank) the cell stays the same.

I'm using Excel 2000, and can't seem to get the combination of these 2
things to give me the results I want. Any ideas?

Thanks.