RE: Searching for corresponding input in two (or more) columns

Hi Lisa,

You could use Conditional Formatting.

This is the formula:
=IF(AND(($C1="B"),($J1<>"S")),TRUE,FALSE)

Now click on cell A1. Go to Format menu -> Conditional Formatting. Under 'Condition 1' section, change the dropbox containing 'Cell Value Is' to 'Formula Is'. Now paste the formula above.

What this is saying is if cell C1 is equal to B and cell J1 is not equal to S, then return 'true' (in other words, DO colour this cell).

Then click on the 'Format...' button and select your desired formatting (eg. red background, see Patterns tab). Press OK when done. Then OK to close the Conditional Formatting box.

Now you need to copy the formatting (includes conditional formatting) to all the other cells. Do this by first ensuring cell A1 is still selected. Press the 'Format Painter' button on your toolbar (see attached screenshot of this if you're not sure). Cell Cell A1's border should now be flashing to indicate you have copied something from it. Now highlight the whole of row 1 only. The formatting will be applied.

Now select all of row 1, and press the 'Format Painter' button again. The whole row should have a flashing border. Now, with the mouse highlight the remaining rows in your spreadsheet.

The formula is updated for each row you copy. Because you used a dollar sign before the C and J, Excel remembers the column, but updates each row automatically.

See attached spreadsheet for a working example. (to see conditional formatting on a cell, select it and go back to Format -> Conditional Formatting).

Any questions, please ask.

Hope this helps. If this is what you're after, please mark this question as resolved.

Regards, Rich

Attached files...

format-painter-screenshot.gif
highlight-b-s.xls