Excel 2007 Conditional formatting with two conditions, one of which is different from the other column

Question

I am using a table in Excel 2007. I am trying to select cells in one column based on its value and its status (which is in another column) in one format.

For example, I am trying to select all cells (only cells, not a row), while the gross profit is negative and has the status "IN". What is the right way to do this? I tried to make statements, but did not have much success. And I'm not sure how to apply the two conditions to the cells. Do I need to select a column or range of cells? Or will I need to select the first cell and copy the format?

Thanks,

Example:

Item # Condition 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 at $ 43.00 11 OUT $ (18.00) 12 at $ 7.00 13 IN $ (25.00)

Average $ 6.00

Answer 1

This works in Excel 2010:

I assume that the cells you want to select are in column C ...

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

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