VBA中如何使用Application.FindFormat查找非黄色填充单元格

Application.FindFormat 仅支持设置需要精确匹配的目标格式属性，不支持使用<>等比较运算符定义否定匹配规则，因此你写的Application.FindFormat.Interior.Color <> vbYellow会直接触发语法错误，无法通过直接设置FindFormat参数实现“查找非黄色格式”的需求。

根据你的数据量大小，可以选以下两种方案：

方案1：直接遍历判断（适合1万单元格以内的小数据量场景） #

逻辑最简单，不需要依赖Find方法，直接遍历已使用区域判断填充色即可，代码容错率高：

Sub SelectNonYellowFilledCells()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim crg As Range, cell As Range, targetRg As Range
    Set crg = ws.UsedRange
    
    For Each cell In crg
        ' 若需要将无默认填充的空白单元格也计入非黄色范围，删除下方判断中 ColorIndex <> xlNone 的条件即可
        If cell.Interior.Color <> vbYellow And cell.Interior.ColorIndex <> xlNone Then
            If targetRg Is Nothing Then
                Set targetRg = cell
            Else
                Set targetRg = Union(targetRg, cell)
            End If
        End If
    Next
    
    If Not targetRg Is Nothing Then
        targetRg.Select
    Else
        MsgBox "未找到符合条件的单元格"
    End If
End Sub

方案2：查找黄色单元格后取差集（适合大数据量场景，效率更高） #

复用你原有查找黄色单元格的逻辑，定位所有黄色填充单元格后，再筛选出非黄色的填充单元格，比全量遍历速度更快：

Sub SelectNonYellowByFindFormat()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim crg As Range, yellowRg As Range, targetRg As Range
    Set crg = ws.UsedRange
    
    ' 先查找所有黄色填充单元格
    Application.FindFormat.Clear
    Application.FindFormat.Interior.Color = vbYellow
    Dim c As Range, firstAddr As String
    Set c = crg.Find(What:=vbNullString, SearchFormat:=True)
    
    If Not c Is Nothing Then
        firstAddr = c.Address
        Do
            If yellowRg Is Nothing Then
                Set yellowRg = c
            Else
                Set yellowRg = Union(yellowRg, c)
            End If
            Set c = crg.Find(What:=vbNullString, after:=c, SearchFormat:=True)
        Loop While c.Address <> firstAddr
    End If
    Application.FindFormat.Clear ' 操作完成后清空查找格式，避免影响后续手动查找
    
    ' 筛选非黄色的填充单元格
    Dim cell As Range
    For Each cell In crg
        If cell.Interior.ColorIndex <> xlNone Then
            If yellowRg Is Nothing Or Intersect(cell, yellowRg) Is Nothing Then
                If targetRg Is Nothing Then
                    Set targetRg = cell
                Else
                    Set targetRg = Union(targetRg, cell)
                End If
            End If
        End If
    Next
    
    If Not targetRg Is Nothing Then
        targetRg.Select
    Else
        MsgBox "未找到符合条件的单元格"
    End If
End Sub

注意：如果工作表中存在条件格式生成的黄色填充，上述代码无法识别，需要额外读取DisplayFormat属性判断显示的填充色。

内容的提问来源于stack exchange，提问作者Leedo