Previous Topic Next Topic

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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 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
Related:

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
Answer 2 / 3
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
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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