Macro with two different sheets

[test1986]

Hi all,

I have a excel workbook with 2 sheets in it. I have tried to write some coding to help me solve my problem but i have had no success. This is why i have come to you guys. OK.....so i have two sheets "Sheet1 and Sheet2". They both have columns of Item Number and Price. In sheet 2 i have some extra Item numbers than in sheet1. So i want the code to look into sheet2 and highlight the item numbers which apear in both sheets.

Hope this makes sense, it seams really complicated to me.

Good Luck

Cheers

[austenr]

You could try something like this:

put this formula in Sheet 2 cell C1 and copy down. If you have over 100 items in sheet 1 you will need to adjust the formula accordingly.

=IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$100,0)),"not match","match")

Then in column C sheet 2, you could auto filter for the ones that have "Match" in that cell.

HTH

[test1986]

Thank-you that works wonders. Now ill write some coding to make that fromulla work automatically.

Thanks again.

[austenr]

glad to help

[CodeMakr]

A VLOOKUP function would also do the trick.

[austenr]

A VLOOKUP can be a suitable substitute but there are sometimes problems with that approach and since I had no knowledge of the OP experience, I suggested the easiest solution I could think of. As with everything, there are many ways to solve any problem, which you quickly find out if you hang around this board long enough IMHO

[CodeMakr]

I'm certainly no expert, so am not aware of the problems with VLOOKUP. Didn't mean to stear in the wrong direction.

[mdmackillop]

As Austen says, a lot of ways to shell a crab!
Here's a VBA solution
Option Explicit
Sub Dupes()
Dim Cel As Range, c As Range, FirstAddress As String
For Each Cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
With Sheets("Sheet2").Columns(1)
Set c = .Find(Cel.Value)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Cel.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next
End Sub

[test1986]

All,

Thanks so much for your help. mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87) vbmenu_register("postmenu_81941", true); thanks for your help that VBA code works wonders and is very tidy. Absolutely Brilliant - Cheers

Test1986