改进了"find duplicates"，但条件格式不起作用

Question

我希望使用条件格式标记重复项，但是我希望在多个列中查找重复项，并标记条目，而不管重复项位于哪个列。在我发现的任何方式中，这都不是本机支持的，所以我制作了自己的UDF。代码列在本文底部。

问题是，虽然UDF按预期工作，但我无法使CF工作。

我有一个CF，我正在根据同一行但不同列的值格式化一些单元格，并且我已经使用公式=INDIRECT("Z" & ROW()) <> 0取得了巨大的成功来实现这一点。在此基础上，我尝试了以下UDF:

=findCandidatesForDuplicate(ADDRESS(ROW(); COLUMN(); 4))
=findCandidatesForDuplicate("B" & ROW())

但是这段代码没有给出任何格式化——无论如何都没有。应用的范围是$B$2:$B$4000，因为这是我想要标记的唯一范围。我尝试在传递字符串到传递范围之间更改UDF和CF输入，但我所做的一切都无法使其应用格式。

我假设由于没有应用格式化，UDF没有在预期的选定单元格上运行，我再次假设这是由于无法使Excel理解我希望它如何解析CF输入引起的。

在工作表中，对于我知道是重复的第14行，我可以输入=findCandidatesForDuplicate("B14")，单元格将显示TRUE。同样地，我可以输入=findCandidatesForDuplicate(ADDRESS(ROW(B14); COLUMN(B14);4))，它也会显示TRUE。

然后问题归结为:我如何使条件格式引擎理解我想做的事情(即运行这个UDF，并将该规则应用于的范围中的每个单元格作为参数，并在B列中标记相应的单元格)?

下面是函数代码。基本上，如果在列出的任何列中发现重复条目，则将该行视为重复项。

Function findCandidatesForDuplicate(rngStr As String, Optional countOnly As Boolean, Optional dbg As Boolean) As Variant
Dim rng As Range
Dim colA As Range, searchString As String, result As Long
Dim ws As Worksheet, tbl As ListObject
Set ws = Application.ThisWorkbook.Worksheets(1)
Set tbl = ws.ListObjects("Tabell1")
Set rng = ws.Range(rngStr)
Set colA = Range("A" & rng.Row)
For i = 3 To 5
    searchString = colA.Offset(0, i - 1).Value
    If searchString = "" Then GoTo NextIteration
    'Set rng = Range(rng.Address, tbl.ListColumns(i).DataBodyRange.Address) ' Only searches downwards from input range
    Set rng = Range(tbl.ListColumns(i).DataBodyRange.Address) ' Searches the entire column
    result = Application.WorksheetFunction.CountIf(rng, "=" & searchString)
    If result > 1 Then
        If dbg = True Then Debug.Print "Found result in loop no. " & i - 2 & ", matching on value " & searchString
        Exit For
    End If
NextIteration:
Next i
If countOnly = True And result > 1 Then
    findCandidatesForDuplicate = result
ElseIf countOnly = True Then
    findCandidatesForDuplicate = 0
ElseIf result > 1 Then
    findCandidatesForDuplicate = True
Else
    findCandidatesForDuplicate = False
End If
End Function

Answer 1

我还没有发现为什么这不起作用，也没有发现如何使起作用，所以这里有一个解决方案:

用调用自己行的UDF填充某个远列，然后使用=INDIRECT("AAA" & ROW()) = whateverValueSetByUDF作为公式，使CF在触发UDF的单元格上显示选定的格式。

它不美观，并且每次发生变化时都需要在包含UDF结果的列上进行全列范围的手动文本到列的更新(过滤表可以正常工作，但如果更改排序，则必须按照描述进行更新)……但只要你知道如何使用表单，它就可以工作。

此外，我修改了代码，以便有更多的选项如何格式化行。使用这种类型的输出，除了查找可能重复的行之外，还可以突出显示正在触发的列。

用法:使用上面的INDIRECT公式，根据您的具体设置进行调整。我建议将=或<>设置为resultWhereString值之一。

Function findCandidatesForDuplicate(rng As Range, Optional countOnly As Boolean, Optional dbg As Boolean) As Variant
Dim colA As Range, searchString As String, result As Long, resultWhereString As String
Dim ws As Worksheet, tbl As ListObject
Set ws = Application.ThisWorkbook.Worksheets(1)
Set tbl = ws.ListObjects("Table1")
Set colA = Range("A" & rng.Row)
For i = 3 To 5
    searchString = colA.Offset(0, i - 1).Value
    ' --> You can add more criteria here
    If searchString = "" Or searchString = "[e-mail]" Then GoTo NextIteration
    Set rng = Range(tbl.ListColumns(i).DataBodyRange.Address) ' Searches the entire column
    result = Application.WorksheetFunction.CountIf(rng, "=" & searchString)
    If result > 1 Then
        If dbg = True Then Debug.Print "Found result in loop no. " & i - 2 & ", matching on value " & searchString
        Select Case i
            ' --> Update this loop if the range of i changes
            Case 3
                resultWhereString = "ResultCol1"
            Case 4
                resultWhereString = "ResultCol2"
            Case 5
                resultWhereString = "ResultCol3"
        End Select
        Exit For
    End If
NextIteration:
Next i
If countOnly = True And result > 1 Then
    findCandidatesForDuplicate = result
ElseIf countOnly = True Then
    findCandidatesForDuplicate = 0
ElseIf result > 1 Then
    findCandidatesForDuplicate = resultWhereString
Else
    findCandidatesForDuplicate = ""
End If
End Function