Excel 2007 Conditional formatting with 2 conditions, one based off another column

Question

I'm using a table in Excel 2007. I'm trying to highlight cells in one column based off of its value and its status (which is in another column) in one format.

For example, I'm trying to highlight all cells (only the cells, not the row) with gross profits that are negative and have a status of "IN". What is the correct way to do this? I've tried if statements, but did not have much success. And I am not sure how to apply two conditions to the cells. Would I need to select the column or range of cells? Or would I have to select the first cell and copy the format?

Thanks,

Example:

Item #  Status  Gross Profit

1   IN   $24.00

2   OUT  $(34.00)

3   OUT  $12.00 

4   IN   $18.00 

5   IN   $(9.00)

6   IN   $(40.00)

7   OUT  $32.00 

8   OUT  $45.00 

9   OUT  $23.00 

10  IN   $43.00 

11  OUT  $(18.00)

12  IN   $7.00 

13  IN   $(25.00)


Average      $6.00 

Answer 1

This works in Excel 2010:

I'm assuming the cells you want to highlight are in column C...

• highlight all cells in column C
• Click Conditional Formatting->Highlight Cells Rules->More Rules
• Choose "use a formula..."
• for the formula, enter : =AND(C2<0, $b2="IN")
• Click the "format" button and choose the highlight color of your choice
• Click OK

Although you've defined the formula in terms of the topmost cell, Excel will generalize to the whole column.