PDA

View Full Version : identifying duplicates on each row



CJW_14
03-13-2019, 11:55 PM
Hi all,

I was wondering if someone can help me with some code.

For each row i'm trying to identifying if the values of 2 cells is the same as the following 2 cells and highlight them. Example below, any help would be much appreciated :)

23898

大灰狼1976
03-14-2019, 01:16 AM
Hi CJW_14!
Something like below:

Sub test()
Dim clm&, rng As Range, i&, j&
For i = 4 To [e65536].End(3).Row
clm = Cells(i, 16384).End(1).Column - 3
For j = 5 To clm Step 2
If Cells(i, j) = Cells(i, j + 2) And Cells(i, j + 1) = Cells(i, j + 3) Then
If rng Is Nothing Then
Set rng = Cells(i, j).Resize(, 4)
Else
Set rng = Union(rng, Cells(i, j).Resize(, 4))
End If
End If
Next j
Next i
If not rng Is Nothing Then rng.Interior.Color = vbYellow
End Sub

CJW_14
03-14-2019, 01:28 AM
You are a legend mate, thanks alot

snb
03-14-2019, 02:29 AM
Or


Sub M_snb()
Sheet1.Cells.UnMerge
sn = Cells(3, 1).CurrentRegion

For j = 3 To UBound(sn)
For jj = 5 To UBound(sn, 2) - 4
If sn(j, jj) & sn(j, jj + 1) = sn(j, jj + 2) & sn(j, jj + 3) And sn(j, jj) & sn(j, jj + 1) <> "" Then
Sheet1.Cells(j + 1, jj).Resize(, 4).Interior.Colorindex = 6
jj = jj + 4
End If
Next
Next
End Sub

Or

Sub M_snb()
Sheet1.Cells.UnMerge
sn = Cells(3, 1).CurrentRegion

For j = 3 To UBound(sn)
For jj = 5 To UBound(sn, 2) - 4
If sn(j, jj) & sn(j, jj + 1) = sn(j, jj + 2) & sn(j, jj + 3) And sn(j, jj) & sn(j, jj + 1) <> "" Then
c00 = c00 & "," & Cells(j + 1, jj).Resize(, 4).Address
jj = jj + 4
End If
Next
Next

If c00 <> "" Then Sheet1.Range(Mid(c00, 2)).Interior.ColorIndex = 6
End Sub



NB. Always use row 1 & Column A !!

CJW_14
03-14-2019, 02:55 AM
Thanks for the code snb, much appreciated