Compare data between sheets and highlight identical (Mac) [Closed]

Report
Posts
2
Registration date
Wednesday June 7, 2017
Status
Member
Last seen
June 14, 2017
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
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

3 replies

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2
Registration date
Wednesday June 7, 2017
Status
Member
Last seen
June 14, 2017

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
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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