CODEWITHSUNDEEP
excel
Kaish
Kaish

Reputation: 255

How to highlight a cell in Excel if the word it contains is in another column

In Excel, I have column 1 with a list of names.

I want to write names in column 2, but not the same names in column 1. How do I format the cells in column 2 to, say change color to red, if the name exists somewhere in column 1?

Thanks

Upvotes: 1

Views: 3733

Answers (1)

Ainz23
Ainz23

Reputation: 56

To do this the easiest way is to create a conditional formatting rule.

  1. In Column A list out all the names
  2. Highlight Column B
  3. Go to Home > Conditional Formatting
  4. Select "New Rule"
  5. Select "Use a formula to determine which cells to format"

  6. In the rule description add the following formula:

    =NOT(ISERROR(MATCH(B1,$A$1:$A$1000,0)))
  7. Click the "Format...." button
  8. Select the "Fill" tab and select the background colour you want (e.g. Red) and click ok

As you type Names into Column B, any names that already exist in Column A will be highlighted in Red.

Upvotes: 3

Related Questions