Conditional Formation based on multiple conditions

[phaniakella]

Dear Experts

I have a excel sheet where the column names are ID,Priority,SUBMIT, REQUEST,AGE (in days). I need to

1. highlight the cells whose REQUEST value is "Incident" and Age is greater than 100 days to Red

2. highlight the cells whose REQUEST value is "Service" and Age is greater than 100 days to Yellow

3. highlight the cells whose REQUEST value is "CR" and Age is greater than 100 days to Gery.

With Conditional formating i am not able to apply conditions and "And" functions.

Please suggest. Thank You.

ID Priority SUBMIT REQUEST AGE
50070 P2 28/10/2010 Incident 203
50828 P2 3/11/2010 Incident 196
52721 P4 24/11/2010 Service 175
53189 P4 1/12/2010 Service 169
54354 P3 14/12/2010 Incident 055
54262 P4 14/12/2010 Incident 056
54706 P2 17/12/2010 Incident 052
55949 P4 10/01/2011 CR 029
56527 P3 13/01/2011 Incident 025


Cheers
Phani

 

[T. Valko]

Dear Experts

I have a excel sheet where the column names are ID,Priority,SUBMIT, REQUEST,AGE (in days). I need to

1. highlight the cells whose REQUEST value is "Incident" and Age is greater than 100 days to Red

2. highlight the cells whose REQUEST value is "Service" and Age is greater than 100 days to Yellow

3. highlight the cells whose REQUEST value is "CR" and Age is greater than 100 days to Gery.

With Conditional formating i am not able to apply conditions and "And" functions.

Please suggest. Thank You.

ID Priority SUBMIT REQUEST AGE
50070 P2 28/10/2010 Incident 203
50828 P2 3/11/2010 Incident 196
52721 P4 24/11/2010 Service 175
53189 P4 1/12/2010 Service 169
54354 P3 14/12/2010 Incident 055
54262 P4 14/12/2010 Incident 056
54706 P2 17/12/2010 Incident 052
55949 P4 10/01/2011 CR 029
56527 P3 13/01/2011 Incident 025


Cheers
Phani

What version of Excel are you using?

 

[phaniakella]

Excel 2007

 

[T. Valko]

Excel 2007

Ok, try this...

Let's assume your data is in the range A2:E10.

Select the *entire* range A2:E10 starting from cell A2. Cell A2 will be the active cell. The active cell is the one cell in the selected range that is not shaded. The formula will be relative to the active cell.

• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
  =AND($D2="incident",$E2>100)
• Click the Format button
• Select the Fill tab
• Select a shade of RED
• OK
• New Rule
• Use a formula to determine which cells to format
• Enter this formula in the box below:
  =AND($D2="service",$E2>100)
• Click the Format button
• Select the Fill tab
• Select a shade of Yellow
• OK
• New Rule
• Use a formula to determine which cells to format
• Enter this formula in the box below:
  =AND($D2="cr",$E2>100)
• Click the Format button
• Select the Fill tab
• Select a shade of GRAY
• OK out