If/Match/then formula

[Saoirse]

I am trying to get a formula or macro to read if a cell in range of workbook 1 matches a cell in range in workbook 2, then highlight row in workbook 2.


So if Cell A:A in workbook 1 matches cell in D:D in workbook 2 then I want the cell (or row if possible) to highlight in workbook 2.

I've tried this =ISNUMBER(MATCH(B:B, 'wkbk1'!B:B, 0)) but it just highlighted the whole column. I've tried conditional formating options but it requires me to do it per cell and I need a range with over 10,000 rows. Any advice would be great. I'm thinking of creating a macro that also opens the second workbook but isn't necessary.

 

[jasonb75]

With conditional formatting you set the rule to a cell and apply it to a range, you don't need to do it for each individual cell.
Assuming that you're starting from row 2, the formula to use would be

=ISNUMBER(MATCH($A2, 'wkbk1'!$D:$D, 0))

If the second workbook will be open then COUNTIF might be more efficient.

Regardless of which formula you use, it would be better to use realistic range sizes instead of full columns. If you only have 10000 rows of data then a full column wastes over 99% of the processing effort.

When you set up the rule it it's easiest to do it with the ranges based on the top left cell of the range to be formated.
Once you have that done you can change the 'Applies to' range to the rows and columns you want to format.

 

[Saoirse]

Thank you! That helped, but now i got another one.

If i look at Worksheet 1 row 2 and I want to view cell in column A and cell in column C then if those both match ANY rows in worksheet 2 I want them to highlight in worksheet 1. Would I just do an isnumber match formula for conditional formatting?

So if I do an =ISNUMBER(MATCH(B1, 'Sheet 1'!A:A,0)) that won't look at both columns. Can I adjust to have it look at two columns and compare to two columns in worksheet 2?

So row 9 on worksheet 1 matches row 5 on worksheet 2. how would I get worksheet 1 to highlight the row since both numbers match?

worksheet 1

amount			identification
10​			3456​
20​			432​
34​			8756​
35​			6532​
37​			9876​
46​			412​
58​			865​
59​			5432​
65​			9865​
35​			4321​
17​			5432​
94​			764​
880​			98723​
56​			654​

worksheet 2

amount		identification
58​		865​
5​		87​
67​		639​
764​		1469​
65​		9865​
78​		3468​
45​		9876​
23​		3457​
45​		87654​
65​		2346​
89​		5423​
98​		88754​
543​		6532​
23​		5432​