Yet another cell formatting question
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Yet another cell formatting question

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sure I'm making this harder than it really is but here's my wish list. I have columns that include some text cells and some data cells. "I wish" for a way to have the font color change to red if a data entry isn't within a certain range. I want to format the entire column this way because I don't know how many entries will be in the column. What I don't wish is for the cells with text to change colors. I know could easily get the data to change to red by highlighting the entire column and using conditional formatting but that changed the cells with text in them to red as well. Example:

    Job No.
    Lot No.
    .305 .005
    .301
    .031
    .306
    .307
    Pass
    Pass
    .308

    So all data in this column between .300 and .310 would be black, text would be black but data <.300 or >.310 would be red.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use Conditional Formatting

    Formula Is

    =AND(ISNUMBER(A8),OR(A8<=0.3,A8>=0.31))

    Check the threshold do you want the >= or just > and <= or just
    Extend or use Format Painter for other cells



    [ This Message was edited by: Dave Patton on 2002-04-04 20:35 ]

    [ This Message was edited by: Dave Patton on 2002-04-04 20:42 ]

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tirolyn:
    Try the formula is:
    =AND(ISNUMBER(A5),OR(A5<0.3,A5>0.31))

    Please post back if it works for you ... otherwise explain a little further and let us take it from there!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave Patton:
    Sorry!, my post may have been just behind yours.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,327
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default



    No reason to say sorry.

    Great minds must think alike.

    I was going to edit my formula and comments after I read the information a second time.

    The >= should be > and <= should be <.



  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good Job Dave ... Cheers!

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dear Guru's,
    You guys are the BEST!! Yes, it works just fine. What a dope I am for forgetting about the format painter..duh.. wouldn't really have mattered though since I didn't know the formula. Anyway I'm thrilled to have found this wonderful resource! I'm sure I'll be back scanning the boards and bothering you more as I try to "idiot proof" our records from data entry errors. Thanks so Much

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