Highlight Cell macro with multiple conditions from another cell

**AFguy** · 2011-10-20, 03:45 PM

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.

**jpr73** · 2011-10-20, 07:05 PM

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** · 2011-10-21, 11:43 AM

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** · 2011-10-21, 01:11 PM

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