title: VBA Color coding the entire column according to drop down list value
body: <p>I have the code in Sheet 1 VBA window. The excel sheet 1 in the workbook is with the drop down list in Column C. The 4 options of the drop down list are: Complete, Pending, Missed Deadline, and Workable. The drop down list is made using Sheet 2 and define name method. However, when I select the value for example, "Complete", the color for the whole row does not turn green. Where am I going wrong?</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  'to make entire row green when job is workable
  If Selection.Text = "Workable" Then
  With ActiveCell
  Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Select
           With Selection.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .Color = 5287936
          .TintAndShade = 0
          .PatternTintAndShade = 0
  
      End With
      End With
  
  ' to make entire row yellow when pending additonal information
  
  ElseIf Selection.Text = "Pending" Then
  With ActiveCell
  Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Select
  
     With Selection.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .Color = 65535
          .TintAndShade = 0
          .PatternTintAndShade = 0
  
      End With
      End With
  'to make entire row red when job is not workable
  
  ElseIf Selection.Text = "Missed Deadline" Then
  With ActiveCell
  Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Select
  With Selection.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .Color = 255
          .TintAndShade = 0
          .PatternTintAndShade = 0
  End With
  End With
  
  
  'to make entire row light blue when job is complete
  
  ElseIf Selection.Text = "Complete" Then
  With ActiveCell
  Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Select
  With Selection.Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorAccent1
          .TintAndShade = 0.799981688894314
          .PatternTintAndShade = 0
  
  End With
  End With
  
   MsgBox "AWESOME!YOU DID IT!"
  
   End If
  
  
  End Sub
  </code></pre>
  
  <p>Please see code and kindly help. Thank you so much!</p>