Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: help with conditional formatting

  1. #1
    New Member
    Join Date
    May 2004
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default help with conditional formatting

    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!!!

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Channel Islands, Britain.
    Posts
    776
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

    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

  3. #3
    New Member
    Join Date
    May 2004
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

    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?

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Channel Islands, Britain.
    Posts
    776
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

    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

  5. #5
    New Member
    Join Date
    May 2004
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

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

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Channel Islands, Britain.
    Posts
    776
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

    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

  7. #7
    New Member
    Join Date
    May 2004
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

    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".

  8. #8
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,426
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

    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()

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Channel Islands, Britain.
    Posts
    776
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

    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

  10. #10
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,426
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: help with conditional formatting

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com