嵌套If / Else / End If在选择案例中 [英] Nested If/Else/End If within Select Case

问题描述

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

我明白，这可以通过条件格式化来实现，并且可以使其工作，但是切割和粘贴（因为用户可能会这样做）会杀死格式。我希望VBA能够解决这个问题。可能还有一些其他解决方案我不知道。

这是我想要发生的事情（所谓的逻辑）

$ b $在Sheet3上的b

  
列（a  -  w）
行（2  -  10485）
在任何字段中更改$ x2或过去的到期日期为$ T2 
 
 if（AND（$ X2 =Open，$ T2，$ T2 <= TODAY（）），则所有行红色（$ a2- $ x $ 2 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ = 完成，然后所有行灰色（$ a2- $ x2）
 = $ X2 =Rescinded，然后$ X2 =橙色和$ A2通过$ W2 =灰色
  / pre> 
 
 
  x 字段将使用一个下拉列表（空白，打开，完成或取消） ）
 
 
 
这是我试图一起滚动并且失败的代码..... 
  Option Explicit 
 Private Sub Worksheet_Change（ByVal Target As Range）
如果相交（目标，范围（A2：X1048567））没有，然后退出Sub 
选择案例细胞（Target.Row， X）。值
案例打开
如果单元格（Target.Row，T）。值<> 而T2< = TODAY（）Then'Range（Cells（Target.Row，A），Cells（Target.Row，F））。Interior.ColorIndex = 3 
 Else 
范围（单元格（Target.Row，A），单元格（Target.Row，F））。Interior.ColorIndex = x1None 
结束选择
 
案例已完成
范围（Cells（Target.Row，A），Cells（Target.Row，F））。Interior.ColorIndex = 15 
案例Rescinded
范围（单元格.Row，A），Cells（Target.Row，F））。Interior.ColorIndex = 15 
范围（Cells（Target.Row，A），Cells（Target.Row，F ））。Interior.ColorIndex = 46 
案例
范围（Cells（Target.Row，A），Cells（Target.Row，F））。Interior.ColorIndex = xlNone 
结束选择
 End Sub 
  
 
 
解决方案
您所描述的内容与您的代码示例指示之间有一些差异，所以我用前者表示。
  Private Sub Worksheet_Change （ByVal Target As Range）
如果不是Inter sect（Target，Range（A：X））Is Nothing Then 
 On Error GoTo safe_exit 
 Application.EnableEvents = False 
 Dim rw As Long，rng As Range 
 For每个rng在相交（目标，范围（A：X））
 rw = rng.Row 
如果rw> 1然后
选择案例LCase（Cells（rw，X）。Value2）
案例open
如果Cells（rw，T）。Value& 和细胞（rw，T）。值<=日期然后
细胞（rw，A）。调整大小（1，24）.Interior.ColorIndex = 3 
 Else 
 Cells（rw，A）。调整大小（1，24）.Interior.Pattern = xlNone 
 End If 
案例完成
单元格（rw，A）。调整大小（1，24）.Interior.ColorIndex = 15 
案例rescinded
单元格（rw，A）。调整大小（1，23）.Interior.ColorIndex = 15 
单元格（rw，X）。Interior.ColorIndex = 46 
 Case Else 
单元格（rw，A）。调整大小（1，24）.Interior.Pattern = xlNone'使用模式关闭内部填充
结束选择
结束如果
下一步rng 
结束如果
 safe_exit：
 Application.EnableEvents = True 
 End Sub 
  
也应该处理多个en尝试像从粘贴一些值到表中收到的那些。通过'白'我假设你想删除任何填充颜色，实际上并不提供白色填充颜色。
 
I have an Excel 2010 form. I am trying to change the row color based on several variables. 

I do understand that this can be accomplished with conditional formatting and have got that to work, but cutting and pasting, as my users will likely do, kills the formatting. I was hoping that VBA would fix that. Possibly there is some other solution I am unaware of.

This is what I want to happen (the so called logic)
 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
The x field will use a drop down and be either ( blank, open, completed, or rescinded )

This is the code I have tried to hobble together and failed with.....
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

解决方案
There were a few discrepancies between what you described and what your code sample indicated so I went with the former.
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
That should also handle multiple entries like those received from pasting a number of values into the sheet. By 'white' I assumed that you meant to remove any fill color, not actually provide a white fill color.

                        
这篇关于嵌套If / Else / End If在选择案例中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！