答案1
您可能对此宏感兴趣。我认为它现在正好满足您的要求。
Sub ColorIndex()
Dim x As Integer
Dim y As Integer
Dim lRows As Long
Dim lColNum As Long
Dim iColor As Integer
Dim iDupes As Integer
Dim bFlag As Boolean
lRows = Selection.Rows.Count
lColNum = Selection.Column
iColor = 2
For x = 2 To lRows
bFlag = False
For y = 2 To x - 1
If Cells(y, lColNum) = Cells(x, lColNum) Then
bFlag = True
Exit For
End If
Next y
If Not bFlag Then
iDupes = 0
For y = x + 1 To lRows
If Cells(y, lColNum) = Cells(x, lColNum) Then
iDupes = iDupes + 1
End If
Next y
If iDupes > 0 Then
iColor = iColor + 1
If iColor > 56 Then
MsgBox "Too many duplicate companies!", vbCritical
Exit Sub
End If
Cells(x, lColNum).Interior.ColorIndex = iColor
For i = 1 To 5
Cells(x, lColNum + i).Interior.ColorIndex = iColor
Next i
For y = x + 1 To lRows
If Cells(y, lColNum) = Cells(x, lColNum) Then
Cells(y, lColNum).Interior.ColorIndex = iColor
For i = 1 To 5
Cells(y, lColNum + i).Interior.ColorIndex = iColor
Next i
End If
Next y
End If
End If
Next x
End Sub
我编辑了代码,使水平线上的颜色也发生变化,直到距离所选单元格 5 个单元格为止。因此,您要做的就是选择数据库的所有名称,然后运行宏。