VBA检查单元格值与格式时报Type Mismatch错误如何解决

问题根因 #

你遇到的类型不匹配错误主要来自三个场景：

• 单元格内存在#N/A、#VALUE!这类错误值时，直接用cell.Value = ""做比较，错误值的类型和字符串不匹配，直接抛出异常
• 当单元格的边框是混合状态（比如同一单元格不同边的边框设置不一致，或者条件格式动态修改了边框），边框的LineStyle、Weight属性会返回Null，Null和任何值做比较都会触发类型不匹配
• 你当前的判断条件没有加括号，VBA默认And优先级高于Or，实际执行的判断逻辑和你预期的逻辑完全不一致，也可能间接触发类型错误

另外你代码里还有一处笔误：cell.Font.C olor中间多了空格，会触发编译错误，需要删掉。

修复后代码 #

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim R1 As Long, G1 As Long, B1 As Long
    Dim R2 As Long, G2 As Long, B2 As Long
    Dim R3 As Long, G3 As Long, B3 As Long
    Dim R4 As Long, G4 As Long, B4 As Long
    Dim cell As Range, My_Range As Range
    
    ' 读取配色参数
    With Worksheets("Final Output")
        R1 = .Range("B27"): G1 = .Range("B28"): B1 = .Range("B29")
        R2 = .Range("B31"): G2 = .Range("B32"): B2 = .Range("B33")
        R3 = .Range("B36"): G3 = .Range("B37"): B3 = .Range("B38")
        R4 = .Range("B41"): G4 = .Range("B42"): B4 = .Range("B43")
        Set My_Range = .Range("C8:G19,I8:N19,P8:U19,W8:AB19,AD8:AI19,AK8:AP19,AR8:AW19,AY8:BD19,BF8:BK19")
    End With
    
    For Each cell In My_Range
        ' 先跳过错误值单元格，避免类型不匹配
        If IsError(cell.Value) Then GoTo NextCell
        
        ' 统一用DisplayFormat获取实际显示的边框属性，先判断不为Null再比较
        Dim bold As Boolean, bottomLine As Long, bottomWeight As Long, topWeight As Long
        bold = cell.DisplayFormat.Font.bold
        bottomLine = IIf(IsNull(cell.DisplayFormat.Borders(xlEdgeBottom).LineStyle), 0, cell.DisplayFormat.Borders(xlEdgeBottom).LineStyle)
        bottomWeight = IIf(IsNull(cell.DisplayFormat.Borders(xlEdgeBottom).Weight), 0, cell.DisplayFormat.Borders(xlEdgeBottom).Weight)
        topWeight = IIf(IsNull(cell.DisplayFormat.Borders(xlEdgeTop).Weight), 0, cell.DisplayFormat.Borders(xlEdgeTop).Weight)
        
        ' 所有逻辑组合加括号明确优先级
        If (bold = True And bottomLine = xlNone) Or (bottomWeight = xlMedium And topWeight = xlMedium) Then
            cell.Interior.Color = RGB(R1, G1, B1)
            cell.Font.Color = RGB(R3, G3, B3)
        ElseIf cell.Value = "" And bottomLine = xlContinuous And topWeight <> xlMedium Then
            cell.Borders(xlEdgeTop).LineStyle = xlContinuous
            cell.Interior.Color = RGB(R2, G2, B2)
            cell.Font.Color = RGB(R4, G4, B4)
        ElseIf (bold = True And bottomLine = xlContinuous) Or (bottomLine = xlContinuous And bottomWeight = xlThin And topWeight <> xlMedium And cell.Value = "") Then
            cell.Borders(xlEdgeTop).LineStyle = xlContinuous
            cell.Interior.Color = RGB(R2, G2, B2)
            cell.Font.Color = RGB(R4, G4, B4)
        ElseIf bottomLine = xlContinuous And bottomWeight = xlThin And cell.DisplayFormat.Borders(xlEdgeTop).LineStyle = xlContinuous And topWeight <> xlMedium Then
            cell.Interior.Color = RGB(R2, G2, B2)
            cell.Font.Color = RGB(R4, G4, B4)
        End If
NextCell:
    Next

End Sub

补充说明 #

你完全可以同时检查单元格格式和值，只需要注意提前处理错误值、Null值这两类特殊场景即可。

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