嵌套的 if/else/end if 在选择案例中

Question

我有一个Excel 2010表单。我正在尝试根据几个变量更改行颜色。

我确实知道这可以通过条件格式完成，并且已经让它工作，但是剪切和粘贴，就像我的用户可能会做的那样，会杀死格式。我希望 VBA 能解决这个问题。可能还有其他一些我不知道的解决方案。

这就是我想发生的（所谓的逻辑）

 on Sheet3  
 Columns (a – w)  
 rows (2 – 10485)  
 upon a change in any field, $x2, or a past due date in $T2
if(AND($X2="Open",$T2<>"",$T2<=TODAY()) then all row red ($a2-$x2)
if(AND($X2="Open",$T2="",$T2>TODAY()) then all row white ($a2-$x2)
=$X2="Completed" then all row grey ($a2-$x2)
=$X2="Rescinded" then $X2 = orange and $A2 thru $W2 = grey

x字段将使用下拉列表，并且为（空白、打开、已完成或已撤销）

这是我试图一起蹒跚而行的代码，但失败了.....

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
     If Intersect(Target, Range("A2:X1048567")) Is Nothing Then Exit Sub
     Select Case Cells(Target.Row, "X").Value
     Case "Open"
     If Cells(Target.Row, "T").Value <> "" And T2 <= TODAY() Then 'Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 3
     Else
     Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = x1None
     End Select
     Case "Completed"
     Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 15
     Case "Rescinded"
     Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 15
     Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = 46
     Case ""
     Range(Cells(Target.Row, "A"), Cells(Target.Row, "F")).Interior.ColorIndex = xlNone
     End Select
End Sub

Answer 1

您描述的内容与代码示例指示的内容之间存在一些差异，因此我选择了前者。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:X")) Is Nothing Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        Dim rw As Long, rng As Range
        For Each rng In Intersect(Target, Range("A:X"))
            rw = rng.Row
            If rw > 1 Then
                Select Case LCase(Cells(rw, "X").Value2)
                    Case "open"
                        If Cells(rw, "T").Value <> "" And Cells(rw, "T").Value <= Date Then
                            Cells(rw, "A").Resize(1, 24).Interior.ColorIndex = 3
                        Else
                            Cells(rw, "A").Resize(1, 24).Interior.Pattern = xlNone
                        End If
                    Case "completed"
                        Cells(rw, "A").Resize(1, 24).Interior.ColorIndex = 15
                    Case "rescinded"
                        Cells(rw, "A").Resize(1, 23).Interior.ColorIndex = 15
                        Cells(rw, "X").Interior.ColorIndex = 46
                    Case Else
                        Cells(rw, "A").Resize(1, 24).Interior.Pattern = xlNone 'use pattern to turn off interior fill
                End Select
            End If
        Next rng
    End If
safe_exit:
    Application.EnableEvents = True
End Sub

这也应该处理多个条目，例如将多个值粘贴到工作表中时收到的条目。通过"白色"，我假设您的意思是删除任何填充颜色，而不是实际提供白色填充颜色。