Data Validation & Conditional Formatting

[Guest]

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.

 

[RichardSchollar]

Hi Shelly

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

=A1>=0

and making sure "Ignore blanks" is checked click OK

3. Go Format>Conditional 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:

=A1>0

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

Hope this helps!

Richard