Excel VBA在活动列中突出显示重复项 [英] Excel VBA Highlight duplicates in active column
问题描述
我正在尝试创建一个宏,它将突出显示输入文本的列中的重复项。
I'm trying to create a macro that will highlight duplicates in the column where text is being entered.
我有54列,并希望在输入文本时突出显示每列中的重复项。场景是:如果在列B中输入STAPLES两次,则单元格(B3,B22)将被突出显示。我想要一个可以为每个列执行此操作的宏,所以如果只有一次没有进行任何操作才会将STAPLES输入到列E中。
I have 54 columns and want to highlight duplicates in each column as the text is entered. The scenario is: if "STAPLES" is entered twice in column B then the cells (B3, B22) would be highlighted. I want a macro that can do this for each column, so if "STAPLES" is entered into column E only once nothing should happen.
使用条件格式化= COUNTIF不一定有帮助(由于将列复制到新工作表的工作流程)。
Using the Conditional Formatting =COUNTIF doesn't necessarily help (due to the workflow of copying columns to new worksheets).
我已经有这个宏:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Dim cel As Range
'Test for duplicates in a single column
'Duplicates will be highlighted in red
Set Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
For Each cel In Rng
If WorksheetFunction.CountIf(Rng, cel.Value) > 1 Then
cel.Interior.ColorIndex = 3
End If
Next cel
End Sub
它可以正常工作,但仅用于一列(C)。
It works ok but is only for one column ("C").
如何将范围设置为活动列?
How do I set the range to be the active column?
我尝试将Rng更改为
'Set Rng = Range(ActiveCell,ActiveCell.Column.End(xlUp))
但这显然是错误的。
任何想法?
推荐答案
>
Try this one:
Set Rng = Range(Cells(1, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
最好使用 Worksheet_Change
事件相反, Worksheet_SelectionChange
。
Btw,有重复的特殊CF:
UPD:
如果您想使用VBA,请尝试以下代码:
UPD: If you'd like to use VBA, try following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim cel As Range
Dim col As Range
Dim c As Range
Dim firstAddress As String
'Duplicates will be highlighted in red
Target.Interior.ColorIndex = xlNone
For Each col In Target.Columns
Set Rng = Range(Cells(1, col.Column), Cells(Rows.Count, col.Column).End(xlUp))
Debug.Print Rng.Address
For Each cel In col
If WorksheetFunction.CountIf(Rng, cel.Value) > 1 Then
Set c = Rng.Find(What:=cel.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 3
Set c = Rng.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End If
Next
Next col
End Sub
这篇关于Excel VBA在活动列中突出显示重复项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!