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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.