sorry if you particular wanted a macro - but conditional formatting would do that
=COUNTIF(Old!$A$2:$A$24,$A2)>0
Book2 |
---|
|
---|
| A |
---|
1 | |
---|
2 | name1 |
---|
3 | name2 |
---|
4 | name3 |
---|
5 | name4 |
---|
6 | name5 |
---|
7 | name6 |
---|
8 | name7 |
---|
9 | name8 |
---|
10 | name9 |
---|
11 | name10 |
---|
12 | name11 |
---|
13 | name12 |
---|
14 | name13 |
---|
15 | name14 |
---|
16 | name15 |
---|
17 | name16 |
---|
18 | name17 |
---|
19 | name18 |
---|
20 | name19 |
---|
21 | name20 |
---|
22 | |
---|
|
---|
Book2 |
---|
|
---|
| A |
---|
1 | |
---|
2 | name1 |
---|
3 | name4 |
---|
4 | name7 |
---|
5 | name10 |
---|
6 | name13 |
---|
7 | name16 |
---|
8 | name19 |
---|
9 | name22 |
---|
10 | name25 |
---|
11 | name28 |
---|
12 | name31 |
---|
13 | name34 |
---|
14 | name37 |
---|
15 | name40 |
---|
16 | name43 |
---|
17 | name46 |
---|
18 | name49 |
---|
19 | name52 |
---|
20 | name55 |
---|
21 | name58 |
---|
22 | name61 |
---|
23 | name64 |
---|
24 | |
---|
|
---|
for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting
Highlight applicable range >>
A2:A100 - Change, reduce or extend the rows to meet your data range of rows
Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=COUNTIF(Old!$A$2:$A$100,$A2)>0
Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK