在Excel中突出显示重复列的VBA代码 [英] VBA code to Highlight duplicate columns in excel

问题描述

我正在开发VBA来验证Excel表单的内容。我想要第一列中的唯一值，并且能够使用另一列上的外键来确定这些值的有效性。这是我必须验证唯一条目：

I am working on a VBA to validate the contents of an excel sheet. I want unique values in the first column, and to be able to determine the validity of these values using the foreign keys on another column. This is what I have to validate for unique entries:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.CountIf(Range("A:A"), Target) > 1 Then
        MsgBox "Duplicate Data", vbCritical, "Remove Data"
        Target.Value = ""
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

它可以防止第一行中的重复条目。但是我真正想要的是通过在已经填充的电子表格上运行宏来检测重复，并且突出显示无效字段。

It prevents duplicate entry in the first row. But what I really want is to be able to detect duplicate by running the macros on an already filled spreadsheet, and to have invalid fields highlighted.

推荐答案

这应该是诀窍：

Sub sbHighlightDuplicatesInColumn()
Dim lastCol As Long
Dim matchFoundIndex As Long
Dim iCntr As Long

lastCol = Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Column
For iCntr = 1 To lastCol
    If Cells(1, iCntr) <> "" Then
        matchFoundIndex = WorksheetFunction.Match(Cells(1, iCntr), Range(Cells(1, 1), Cells(1, iCntr)), 0)
        If iCntr <> matchFoundIndex Then
            Sheets("Sheet1").Cells(1, iCntr).Interior.Color = vbYellow
        End If
    End If
Next
End Sub

这篇关于在Excel中突出显示重复列的VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！