excel - 如何通过 VBA 宏基于单个单元格中的值突出显示重复值？

Question

我有六列，如名称、分配、组、角色、地理和 ID，我想根据单元格值突出显示同一 ID 中的重复值。该单元格有多个带有分号的值。如果任何单元格值与其他单元格值匹配，则应突出显示两行。

原始文件视图 结果视图

我已经为它编写了一个宏，但它不起作用。请协助。

    Sub DuplTTM()
    'select first row data and compare with the table
     Dim i As Long, j As Long, nextrow As Long, lastrow As Long, nwsheet As Worksheet, r As Integer, 
    c1 As Integer, c2 As Integer, c3 As Integer

    ' sorting data by ID

    Columns("A:F").Sort key1:=Range("F2"), _
    order1:=xlAscending, Header:=xlYes

   ' finding the last row
   lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

   'random colour generation
   c1 = Int(255 * Rnd) + 1
   c2 = Int(255 * Rnd) + 1
   c3 = Int(255 * Rnd) + 1
   mycolor = RGB(c1, c2, c3)
   Application.ScreenUpdating = False
  j = 3
   ' defining next row with expected result
  For i = 2 To lastrow

  arrv = Split(ActiveSheet.Cells(i, 3), "; ")
   arrn = Split(ActiveSheet.Cells(j, 3), "; ")

 If ActiveSheet.Cells(i, 6).Value = ActiveSheet.Cells(j, 6).Value Then
        For Each k In arrv
            For Each l In arrn
        If (Trim(k) = Trim(l)) = True Then
            If ActiveSheet.Cells(i, 2).Value <> ActiveSheet.Cells(j, 2).Value Then
            ActiveSheet.Cells(i, 1).Resize(1, 6).Interior.Color = mycolor
            ActiveSheet.Cells(i + 1, 1).Resize(1, 6).Interior.Color = mycolor
            Exit For
            End If
        End If
            Next l
        Next k
End If
j = j + 1
  Next i

 Application.ScreenUpdating = True

MsgBox ("Completed")
End Sub

标签： excelvba