title: Nested If/Else/End If within Select Case
body: <p>I have an Excel 2010 form. I am trying to change the row color based on several variables. </p>
  
  <p>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.</p>
  
  <p>This is what I want to happen (the so called logic)</p>
  
  <pre><code> 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&lt;&gt;"",$T2&lt;=TODAY()) then all row red ($a2-$x2)
  if(AND($X2="Open",$T2="",$T2&gt;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
  </code></pre>
  
  <p>The <code>x</code> field will use a drop down and be either ( blank, open, completed, or rescinded )</p>
  
  <p>This is the code I have tried to hobble together and failed with.....</p>
  
  <pre><code>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 &lt;&gt; "" And T2 &lt;= 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
  </code></pre>