How do you highlight text in a column based on adjacent values?

[max3732]

Let's say you have one column with names and the column next to hit either has an "a" or "b" next to it. How would you highlight all the names with an "a" in the cell next to them?

I tried conditional formatting and could see how to create a new rule to do it if there was a number in original column, but how do you do it with names?

[jeffreybrown]

Try this...

I've used A1:B200 as the range, but choose what your range reflects.

Names in column A and "a" or "b" in column B

Conditional Formatting

• Highlight applicable range >> A1:B200
• Home Tab >> Styles >> Conditional Formatting >> New Rule
• Select a Rule Type: Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND(LEN($A1)>0,$B1="a")
• Format… [Number, Font, Border, Fill]
• OK >> OK

[Pete_UK]

First you should select all the cells in column A that you want this to apply, so that you only need to set it up once - assume this range is from A2 to A100.

Then you can click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box that pops up:

=AND(A2<>"",$B2="a")

Click on the Format button | Fill tab and choose your colour, then OK your way out. Excel will automatically adjust the cell references to suit all the cells that had been selected.

Hope this helps.

Pete

[Pete_UK]

Thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete