Results 1 to 6 of 6

Thread: Highlight Cell macro with multiple conditions from another cell

  1. #1

    Highlight Cell macro with multiple conditions from another cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I am trying to highlight cells based on multiple values of the cell itself and multiple values of another cell. For example, cell L5 has a list of three words (emergency, urgent, routine) and I want to highlight E5 red if emergency is selected and E5 has a value of more than 2 (days). E5 shows the number of days elapsed from two other cells. Then, if urgent is selected from L5, and E5 is greater than 14, highlight E5 red. Same with routine if E5 is greater than 365. If E5 is less than those numbers, highlight in green. I have to do this for E5 and L5 thru E632 thru L632. I have tried using conditional formatting with no avail. I have attached a copy of my spreadsheet. Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Seeker jpr73's Avatar
    Join Date
    Apr 2011
    Location
    France
    Posts
    15
    Articles
    0
    Hay,
    First conditional format :
    =ET(L5="EMERGENCY";E5>2) Format cell = Red
    Second and third conditional format :
    =ET(L5="URGENT";E5>14) Format cell = Red
    Fourth and fifth conditional format :
    =ET(L5="URGENT";E5<365) Format cell = Green
    You can see that there is a problem with the second-third and fourth-fifth hypothesis ?
    Best regards

  3. #3
    If I did CF, wouldn't I have to do that for every row? That wouldn't be feasible, I have 600+ rows to format. I am familiar with excel until it comes to somewhat complex formulas and then I have to ping the experts. Thank you.

  4. #4
    Seeker jpr73's Avatar
    Join Date
    Apr 2011
    Location
    France
    Posts
    15
    Articles
    0
    Hay,
    In the window of CF you have 4 columns : rule, format, applies to, interrupt if true.
    As you can see the column applies to permit to select every row you want
    Best regards
    Attached Files Attached Files

  5. #5
    It worked perfect, thank you! Once I pulled my head outta my butt, it made sense. Been a long week, lots of projects. Thanks again.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,087
    Articles
    79
    Blog Entries
    14
    Cool deal. Thanks for pitching in to help jpr73!
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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