Compare data between sheets and highlight identical (Mac)

Closed
Jeannus
Posts
2
Registration date
Wednesday June 7, 2017
Status
Member
Last seen
June 14, 2017
- Updated on Jun 12, 2017 at 12:10 PM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jun 15, 2017 at 11:32 AM
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

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 12, 2017 at 12:05 PM
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
Jeannus
Posts
2
Registration date
Wednesday June 7, 2017
Status
Member
Last seen
June 14, 2017

Jun 14, 2017 at 04:06 AM
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
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 15, 2017 at 11:32 AM
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
0