Highlight Cell macro with multiple conditions from another cell

AFguy

New member
Joined
Oct 20, 2011
Messages
3
Reaction score
0
Points
0
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.
 

Attachments

  • Book1.xlsm
    107 KB · Views: 48

jpr73

New member
Joined
Apr 11, 2011
Messages
15
Reaction score
0
Points
0
Location
France
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
 

AFguy

New member
Joined
Oct 20, 2011
Messages
3
Reaction score
0
Points
0
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.
 

jpr73

New member
Joined
Apr 11, 2011
Messages
15
Reaction score
0
Points
0
Location
France
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
 

Attachments

  • Book1_jpr73.xlsm
    108.1 KB · Views: 80

AFguy

New member
Joined
Oct 20, 2011
Messages
3
Reaction score
0
Points
0
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.
 
Top