+ Reply to Thread
Results 1 to 11 of 11

Conditional formatting with two IF criteria

  1. #1
    Registered User
    Join Date
    09-30-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Conditional formatting with two IF criteria

    I've read through a bunch of this forum's postings and googled, but cannot find an answer, so some help would be greatly appreciated.

    Here's the situation: I'm a teacher and I need to determine whether my students meet the requirements to go on an internship abroad. Some requirements are simple, such as having completed all the course work from the first year. I can do this quite easily and it works. However, should a student wish to do the internship in a language other than English, he needs to meet more detailed requirements:
    If the student is currently at level 3 in this language, he needs to complete this level with at least a 7.
    If the student is currently at level 5 in this language, he needs to just pass this level, which is a 5.5.

    I have two columns, one for level and one for score. What I'd like to do is change the color of the score cell green if the student meets the requirements and red if he doesn't. I've tried this in a number of ways, with regular conditional formatting, with some IF formulas, but I can't get it to work. It's probably fairly easy for the more experienced Excel users out there, but it totally baffles me.

    Thanks a million!

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,055

    Re: Conditional formatting with two IF criteria

    Can you upload example workbook?
    Never use Merged Cells in Excel

  3. #3
    Registered User
    Join Date
    09-30-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Conditional formatting with two IF criteria

    Yes, of course, sorry.

    As you can see, I already worked out the requirements for the first year (prop): the cell turns green when the student reaches 60 credits. For English they need a passing score, so the cell turns green when it's 5.5 or higher and red if below.

    Students must choose two of three other languages offered (French, German or Spanish) which is why I have three language columns. But once I get it working for one of them, I can make it happen for the other ones.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-14-2008
    Posts
    153

    Re: Conditional formatting with two IF criteria

    You need to use conditional formatting based on the value of another cell.
    Find details here:

    http://www.exceldigest.com/myblog/20...-another-cell/

    For the first case (level 3, score 7)

    Select the first cell in the scores column and Enter 2 rules:

    Rule 1. give this green color

    Please Login or Register  to view this content.
    Rule 2. give this a red color.
    Please Login or Register  to view this content.
    In the Conditional formatting Applies to column specify all the range in the scores column.

    Repeat the scenario for the second case.
    Last edited by se1429; 09-30-2012 at 07:32 AM.
    Welcome to: http://www.exceldigest.com/myblog/
    "Excel help for the rest of us"

  5. #5
    Registered User
    Join Date
    09-30-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Conditional formatting with two IF criteria

    Thanks so much for you quick replies.

    se1429, I found your post while googling for the answer, but wasn't able to derive it from that. I have put in the formulas as suggested, but nothing happens. The score cell remains white. I attached the new version. I must have made a mistake somewhere but I don't see it.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    06-14-2008
    Posts
    153

    Re: Conditional formatting with two IF criteria

    Here are two of your formulas that need changes:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    You forgot to change A2 and B2 to G2 and H2 respectively.
    I hope this will fix the problem.
    Last edited by se1429; 09-30-2012 at 10:15 AM.

  7. #7
    Registered User
    Join Date
    09-30-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Conditional formatting with two IF criteria

    se1429, I put these in, changed to G2 and H2 obviously, but nothing happens. Cell remains white.

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,055

    Re: Conditional formatting with two IF criteria

    I've tried your formula and entered values in A (5) and B (6) column and it remain white.
    Since it should work I just reentered your formula and then it worked.

    So try that.

    Also, and advice, you don't need to make conditional formatting for blank cells or something.
    If none of criteria will be met then your cell will have default color (white ie blank)

  9. #9
    Registered User
    Join Date
    09-30-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Conditional formatting with two IF criteria

    I tried again, but it still doesn't work. I honestly don't know what's wrong.

    For the other cells I had to put in the condition regarding empty cells because otherwise it would turn all empty ones red.

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,055

    Re: Conditional formatting with two IF criteria

    Here. Works for me.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-30-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Conditional formatting with two IF criteria

    Yes, it works! I don't know why it wouldn't before, but it's fixed. Thank you all very much! And now I should know how to do such a thing myself in future.

+ 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