# Matching Data Lists

#### clilley113

##### New Member
Hi,

I have two lists of data over two excel sheets within a workbook. ('sheet 1' & 'Matured')

What I would like to do is input some form of formatting that will highlight any cell on 'sheet one' that contains a value that is on the 'Matured' sheet.

'Sheet 1's data is in cells U2 to U2474

'Matured' data is in cells B2 to B181

Does any one have any suggestions?

C

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### 63falcondude

##### Well-known Member
Re: Matching Data Lists - HELP!

Check out Conditional Formatting. Highlight U2:U2474 of Sheet 1, Conditional Formatting, New Rule, Use a Formula,

=COUNTIF(Matured!B\$2:B\$181,U2)

Format Fill whatever color you like.

I haven't tested this formula but it should work. Let us know.

#### clilley113

##### New Member
Re: Matching Data Lists - HELP!

Amazing that worked perfectly!

One more question...

If i want to highlight any rows that are duplicated, say from row starting A2 - row A2074 how would i do that?

Thanks so much for your help!

C

#### mesco019

##### New Member
Re: Matching Data Lists - HELP!

Hello there!

to highlight any duplicated value in the row you can use a conditional rule, first you have to select the range that you would like the rule to be apply, after you go to create a rule, you will see an option that will say "apply format to unique or duplicated values" and then select duplicated values and the color that you will like to have

#### clilley113

##### New Member
Re: Matching Data Lists - HELP!

If i do that it wont only highlight only entire rows that match it will only do it by cell.

Is there a way you an only highlight entire rows that match?

Thanks

C

Replies
1
Views
340
Replies
19
Views
513
Replies
3
Views
170
Replies
2
Views
186
Replies
0
Views
78

1,186,986
Messages
5,960,961
Members
438,504
Latest member
KristofGoossens

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back