CODEWITHSUNDEEP
excel
Tiago
Tiago

Reputation: 653

Excel Conditional Formatting if number from range 1 exists in range 2

I'm getting some trouble using conditional formatting to conditional formatting a range of cells that exists in another range of cells.

For example I have this:

1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

In another sheet, I have this:

3 9 50 53 69
17 21 29 50 53

If I use conditional formatting for all 3 rows in frist code "Cell value equal to" range it doesn't work, saying I need to select a specific cell. What would be the way to formatting this automatically?

Please consider two different sheets and starting from A to E.

Thanks

Upvotes: 0

Views: 150

Answers (1)

Jchang43
Jchang43

Reputation: 891

If you select the desired range (A1:E2) in the second sheet and add a new rule that "uses a formula to determine which cells to format", you can use the following formula

EDIT: Removed excess formula as per Scott's recommendation

=COUNTIF(Sheet1!$A$1:$E$3,A1)>0

and it should apply to any of the cells that are the same as the cells in the first sheet.

Upvotes: 1

Related Questions