Report

Compare data between sheets and highlight identical (Mac)

Ask a question Jeannus 2Posts Wednesday June 7, 2017Registration date June 14, 2017 Last seen - Last answered on Jun 15, 2017 at 11:32 AM by TrowaD
Hi,
Macros aren't my specialty and I need to Highlight on sheet 1 all the identical products that sits on sheet 2. i.e. :

Sheet 1
Product A
Product B

Sheet 2
Product A
Product C

So, Product A highlighted in sheet1

Please help me, I am quite desperate...:(
Thank you

ps. I am working on Mac
See more 
Helpful
+1
plus moins
Hi Jeannus,

Give the following code a try:
Sub RunMe()
Dim mFind As Range

Sheets("Sheet1").Select

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet2").Columns("A").Find(cell.Value)
    If Not mFind Is Nothing Then
        cell.Interior.ColorIndex = 3
    End If
Next cell
End Sub

The identical products will be highlighted in red. That is what the colorindex=3 stands for. When you want a different color then take a look a the table below and pick a number matching the color you want.



Hopefully this will work on a mac.

Best regards,
Trowa
Leave a comment
Helpful
+0
plus moins
Hello Trowa,
Thank you very much indeed!
It works fine, even on a mac... :)
Can I ask you as well how to highlight as a second step the similar products but with a difference in column 2

So:
Sheet1:
Product A; 10
Product B; 11
Product C; 6

Sheet 2:
Product A;11
product b; 11
Product D; 7

Then Product A & B highlighted in red (as you have already kindly done) and then highlighted in blue the second information if identical, in this case: product B;11

I know it adds up to your time but I have to take on Sage as well in my company and my schedule is becoming really tight...

Thank you very much again,
Jeannus
Leave a comment
Helpful
+0
plus moins
Hi Jeannus,

Good to see it worked for you on the Mac!

So in your given example you want to highlight Product A and B in red and 11 in blue on sheet1.

In that case use the following code:
Sub RunMe()
Dim mFind As Range

Sheets("Sheet1").Select

For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Set mFind = Sheets("Sheet2").Columns("A").Find(cell.Value)
    If Not mFind Is Nothing Then
        cell.Interior.ColorIndex = 3
        If cell.Offset(0, 1).Value = mFind.Offset(0, 1).Value Then
            cell.Offset(0, 1).Interior.ColorIndex = 5
        End If
    End If
Next cell
End Sub


Best regards,
Trowa
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!