CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007conditional-formatting
PIYUSH
PIYUSH

Reputation: 11

compare data in 2 excel spreadsheets and then highlight common data in one excel

I have a excel sheet with 2 tabs . In each tab there are some text written.

I need to compare values in column_1 in sheet 1 with values in column_2 of sheet2 and in case any similar values present I need to highlight the text in sheet1.

Also, suppose I copy some data from sheet1 to sheet2 (column_2) then shee1(column_1) need to be highlighted.

Note : 1. This should work only via conditional formatting or any other formulas. I cannot use any macro

  1. Don't mention that copy sheet1 and sheet2 in one sheet and apply formula. As I don't want to use that approach

Upvotes: 0

Views: 124

Answers (1)

pnuts
pnuts

Reputation: 59475

Select column_1 in sheet 1 and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=COUNTIF(sheet2!$B:$B,A1)

Format..., select colour Fill (highlight) of your choice, OK, OK.

When copying from column_1 in sheet 1 to sheet2 the formatting may be transferred unless the choice is made not to.

Upvotes: 1

Related Questions