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.
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
MsgBox “Successfully completed”