finding duplicate values from two sheets…..

I have two sheets in EXCEL

data like these….

sheet1 : 2000, 3000, 4500, 300, 2000, 3000       
sheet2: 300, 2000, 3000, 4550

I run the following code to highlight the values in both sheets with different colours, where value meets with critiera.

but problem is values of sheet1 all 2000, 3000 fillied with colors, whereas sheet2 having 2000, 3000 only one time.

my need is, if compare with to sheet 2 it contains values 2000, 3000 only one time
so sheet1 values first and second fill with colors, rest of values (last two values) should not be in color.

great thanks for solution.Laughing

Sub Dupranges()

Dim wr1 As Range, wr2 As Range, Rng1 As Range, Rng2 As Range

Set wr1 = Worksheets(“Sheet1”).Range(“f1:f10”)
Set wr2 = Worksheets(“Sheet2”).Range(“g1:g10”)

For Each Rng1 In wr1
    Rng1.Value = Rng1.Value
    For Each Rng2 In wr2
        If Rng1.Value = Rng2.Value Then
            Rng1.Interior.ColorIndex = 43
            Rng2.Interior.ColorIndex = 33
            Exit For
        End If
    Next
Next

MsgBox “Successfully completed”

End Sub

By: Raj

Leave a Reply

Your email address will not be published. Required fields are marked *