Highlighting cells in column A if column B contains the correct value in Excel

Question

I am trying to get a cell in column A to be highlighted if the corresponding cell in column B contains a certain value. It's easy enough conditional formatting column B on its own but I would like to carry the colour across to column A no matter what value is in the A column.

Say "Joe" is working sat, sun, mon and not working tue, wed, thu, fri, his name is in column B across from the the days he is working labeled in column A. I would like to highlight the days as well as his name whenever he is "working".

I hope this makes sense. I look forward to hearing if anybody has created or used a formula in this way before.

Answer 1

You can set conditional formatting in A2 based on a formula, so set the formula to something like the following:

=B2="Joe"

To copy over the conditional formatting to the other cells, I selected the range and used Ctrl+Y to redo the conditional formatting. It changed the formula to match the current cell.

Note: I did this in Excel XP, as I don't have Excel 2010 in front of me.

Answer 2

To format both columns (as requested), select ColumnA:B, clear existing CF rules from them and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=$B1="Joe"

Format..., select choice of formatting, OK, OK.

For me also Ctrl+Y does not here work for copying down, let alone the copy across aspect.