VBA多条件触发单元格样式变更的Case语句代码精简优化求助

优化思路 #

我们可以把两个位置的允许取值分别存为数组，通过Filter函数快速判断取值是否在允许范围内，完全替代冗余的Case枚举，后续调整条件时只要修改对应数组的元素即可，不用逐个罗列所有组合。

优化后完整代码 #

Sub PairedCell3()
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    Dim C As Range, rng As Range
    ' 所有允许的取值集中定义在此处，后续调整条件直接修改数组即可
    Dim firstAllowArr, secondCommonAllowArr, secondSpecialAllowArr_N
    firstAllowArr = Array("E", "N")
    secondCommonAllowArr = Array("D", "D1", "D2", "D3", "D4", "D5", "G", "K")
    secondSpecialAllowArr_N = Array("E") ' 第一个值为N时额外允许的第二个单元格取值
    
    Set rng = Range("C3", Range("AL" & Rows.Count).End(xlUp))
    
    ' 统一设置基础细边框
    With rng.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    ' 遍历单元格做判断
    For Each C In rng
        ' 跳过最右列，避免取右侧单元格时报越界错误
        If C.Column < Columns("AL").Column Then
            Dim firstVal As String, secondVal As String, matchFlag As Boolean
            firstVal = UCase(C.Value) ' 统一转大写，避免单元格内容大小写不一致导致判断失效
            secondVal = UCase(C.Offset(0, 1).Value)
            matchFlag = False
            
            ' 判断是否满足条件1：第一个值在允许列表，第二个值在通用允许列表
            If UBound(Filter(firstAllowArr, firstVal)) > -1 And UBound(Filter(secondCommonAllowArr, secondVal)) > -1 Then
                matchFlag = True
            ' 判断是否满足条件2：第一个值为N，第二个值为特殊允许的E
            ElseIf firstVal = "N" And UBound(Filter(secondSpecialAllowArr_N, secondVal)) > -1 Then
                matchFlag = True
            End If
            
            ' 匹配到条件则修改样式
            If matchFlag Then
                With C.Resize(, 2)
                    .Borders.LineStyle = xlContinuous
                    .Borders.Weight = xlThick
                    .Borders.Color = RGB(100, 0, 255)
                End With
            End If
        End If
    Next C
    
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

优化说明 #

• 所有规则配置集中在代码开头，后续增删判断条件不需要修改逻辑代码，维护成本极低
• 完全去掉了冗余的Case枚举，不会因为漏写某个组合导致判断出错
• 增加了大小写兼容、边界判断的容错处理，运行稳定性更高

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