help with conditional formatting

[IOTP]

I'm trying to get an excel invoice to recognize negative numbers. I only have one condition running and it is "cell value is between -1000 and 1000" the format of the cell is currency, but it's still not recognizing negative numbers. can anyone help please!!!

 

[jimbojones]

Little sparse on information.

Have you checked the conditional formatting is set up correctly. When I tried it mone worked OK...even with Currency formatting.

James

 

[IOTP]

Yes, I've checked that the formatting was done correctly. Do you have an idea of where I might have gone wrong? I selected the cells that contain the dollars, clicked on conditional formatting and set it to what I said in the first post. Does that help?

 

[jimbojones]

OK that should work....you have also selected a format that you want to apply when the condition is met right?

i.e.

1. click on cell with value in
2. Format --> conditional formatting
3. cell Value is...
4. Between
5. -1000
and..
6. 1000
7. Click on format and apply a relevant format.
Then click ok.

Works fine for me.


James

 

[IOTP]

YEP, did all that and it still doesn't recognize the negative number as a number.

 

[jimbojones]

Running out of ideas here...

OK so it has to be the number in the cell that is causing the problem. What is the formatting of this number and have you tried entering it directly i.e. keying in a negative number?


James

 

[IOTP]

it is formatted as currency with two decimal places and a negative sign in front for negative numbers. When I type a number in it says "Unit price errror, you must enter a number into this cell".

 

[GorD]

As Jimbojones says, there's no conditional formatting reason reason why it won't work

Try adding another number to that cell and see what you get. i.e in another cell put =cell in question + 5. Does it give an answer or an error.?

Also what formating are you trying to apply, if you just want the negative value to show as red text, you don't need conditional formatting, but you probably know that


I tried to give an easy way to test if it was a number, however I have realised that it doesn't work as it adds okay. Use the ISNUMBER()

 

[jimbojones]

Right maybe now we are getting somewhere.

Sounds like you have applied data validation to the cell.

Check under Data --> Validation


This might have to be my last try before I leave for the night!!!!


James

 

[GorD]

That now sounds like a data validation problem. Is cell entry restricted by data validation?