+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting (IF statement)

  1. #1
    Registered User
    Join Date
    06-09-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    2

    Conditional Formatting (IF statement)

    I've only recently discovered conditional formatting and have used it successfully with simple formulas. Now I need to do something more complicated.

    I have 2 columns and here is what I'm trying to make happen.

    If the value in column A is 1 or 2 and the value in column B is > 1.15, I want the cell text in column B to be red.
    If the value in column A is 1 or 2 and the value in column B is < .85, I want the cell text in column B to be green.
    If the value in column A is 1 or 2 and the value in column B is between .85 and 1.15, I want the cell text in column B to be black.

    If the value in column A is 3 and the value in column B is > 1.1, I want the cell text in column B to be red.
    If the value in column A is 3 and the value in column B is < .9, I want the cell text in column B to be green.
    If the value in column A is between .9 and 1.1, I want the cell text in column B to be black.

    I'm driving myself nuts trying to figure it out, so perhaps an expert on this board can assist. Hope so!

    Thanks.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,465

    Re: Conditional Formatting (IF statement)

    See if this will get you started?

    =OR(B1>1.15,A1=1,A1=2) format text RED
    You need to create a new rule for each condition

    Shout if you still have a problem
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-09-2013
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Conditional Formatting (IF statement)

    I tried that formula, but it made the entire column red.

    So then I kept inputting the other criteria and when I got to one that makes the text green, the entire column turned green.

    Not sure what I'm doing wrong.

  4. #4
    Registered User
    Join Date
    06-01-2013
    Location
    Columbus, ms
    MS-Off Ver
    Excel 2013
    Posts
    68

    Re: Conditional Formatting (IF statement)

    The attached conditions seem to work. I would point out a couple of things, though...

    Your last option overrides your first anytime the value is in column A=1. You can determine which one is applied by the order you place the conditional formatting in, though.

    You do no account for what happens when the value is equal to 1.15. Do you want that to turn black or red? if black then the back condition should be "<=" rather then just "<".

    one thing to keep in mind with conditional formatting is that you must build your conditional formatting rule based off the Active cell.

    conditionalFormatingSamples.xlsx

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,465

    Re: Conditional Formatting (IF statement)

    Perhaps it would help if you provided us with a sample workbook (no sensitive data?
    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1