[excel2007] highlight duplicates.

[montecarlo2012]

If any cell on Sheet1 B2:F2 match any cell on sheet 2 B2:F2 then highlight the duplicates on sheet 2
If any cell on sheet1 B3:F3 match any cell on sheet 2 B3:F3 then highlight the duplicates on sheet 2
If any cell on sheet1 B4:F4 match any cell on sheet 2 B4:F4 then highlight the duplicates on sheet 2
If any cell on Sheet1 B5:F5 match any cell on sheet 2 B5:F5 then highlight the duplicates on sheet 2
If any cell on sheet1 B6:F6 match any cell on sheet 2 B6:F6 then highlight the duplicates on sheet 2
If any cell on sheet1 B7:F7 match any cell on sheet 2 B7:F7 then highlight the duplicates on sheet 2

And do this all the way down to the last row.
any help, please.
thanks for reading this.

 

[mohadin]

Hi
Try

Sub test()
    For i = 2 To Sheets("sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
        With Sheets("sheet2")
            For ii = 1 To 5
                a = Application.Transpose(.Cells(i, 2).Resize(, 5))
                b = Application.Transpose(Sheets("sheet1").Cells(i, 2).Resize(, 5))
                x = Application.Match(b(ii, 1), a, 0)
                If IsNumeric(x) Then
                    .Cells(i, x + 1).Interior.Color = vbRed
                End If
            Next
        End With
    Next
End Sub

 

[montecarlo2012]

mohadin, thank you for your time,
didn't work.

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim i As Long, r As Long, c As Long, sh1 As Worksheet, sh2 As Worksheet
    Dim v1 As Variant, v2 As Variant, val As String
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    v1 = sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
    v2 = sh2.Range("B2", sh2.Range("B" & Rows.Count).End(xlUp)).Resize(, 5).Value
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2) & "|" & v2(i, 3) & "|" & v2(i, 4) & "|" & v2(i, 5)
        For r = LBound(v1) To UBound(v1)
            For c = LBound(v1, 2) To UBound(v1, 2)
                If InStr(1, val, v1(r, c)) > 0 Then
                    sh2.Cells(r + 1, c + 1).Interior.ColorIndex = 3
                End If
            Next c
        Next r
    Next i
    Application.ScreenUpdating = True
End Sub

 

[awoohaw]

Do you need to do this with VBA, I believe conditional formatting was available in Excel 2007.
Try this conditional formatting rule on the cells in Sheet2, starting in cell B2 and highlight all your cells in the range:

=ISNUMBER(MATCH(B2,Sheet1!$B2:$F2,0))

Sheet 1:

Book1
	A	B	C	D	E	F	G
1
2		LOG	MHM	XRN	BPL	CLY
3		UJC	OZZ	KHB	YWN	IPX
4		ZTO	JHJ	JQQ	AMQ	UKS
5		ZJS	IWR	BWZ	DIO	PNS
6		RPF	XQN	GFR	XZC	ALH
7		NQB	PEG	LCI	AEE	DMK
8
Sheet1

Sheet2:

Book1
	A	B	C	D	E	F	G
1
2		QGC	BPL	CLY	BLC	RCN
3		TVN	TEM	DAR	NVV	DIX
4		LYQ	JMQ	QUR	MUD	GOZ
5		VGP	CXH	DJW	LBO	ZJS
6		QTT	UJT	MNK	YNM	PHY
7		ITJ	CXW	BIN	FXZ	POB
8
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:F7	Expression	=ISNUMBER(MATCH(B2,Sheet1!$B2:$F2,0))	text	NO

 

[mohadin]

didn't work.

But It does work in here
And you did not mentioned any error message
Any way would you please try this

Sub test()
Dim i&, ii&
Dim a, b, x
    For i = 2 To Sheets("sheet2").Cells(Rows.Count, 2).End(xlUp).Row + 1
        With Sheets("sheet2")
            For ii = 1 To 5
                a = Application.Transpose(.Cells(i, 2).Resize(, 5))
                b = Application.Transpose(Sheets("sheet1").Cells(i, 2).Resize(, 5))
                x = Application.Match(b(ii, 1), a, 0)
                If IsNumeric(x) Then
                    .Cells(i, x + 1).Interior.Color = vbRed
                End If
            Next
        End With
    Next
End Sub

 

[montecarlo2012]

I figure out.

Sub LToL()
    ' HIGHLIGHT THE MATCHES
    Dim rng As Range, fnd As Range
    Dim lastRow As Long
    Dim startingRow As Long

    ' Find the last row in column B on "Sheet1"
    lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "B").End(xlUp).Row

    For startingRow = 2 To lastRow ' Loop from row 2 to the last row on "Sheet1"
        For Each rng In Sheets("Sheet1").Range("B" & startingRow & ":F" & startingRow) ' READ columns B to F on "Sheet2"
            ' Search for the value in columns B to F on "Sheet2"
            Set fnd = Sheets("Sheet2").Range("B" & startingRow + 1 & ":F" & startingRow + 1).Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)  ' HIGHLIGHT
            If Not fnd Is Nothing Then
                fnd.Interior.ColorIndex = 6 ' Change color to yellow
            End If
        Next rng
    Next startingRow
End Sub

thanks
Awoohaw, Mohadin and Mumps for your time
I work out on it.