title: Private sub worksheet events with different ranges
body: <p>I know that you can have only one private sub worksheet change event however I am struggling to combine code for the events I need. I am new to VBA so any assistance or recommendations are appreciated. Would it be more efficient to use select case?</p>
  
  <p>First code needed: </p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal target As Range)
  
  Dim cell As Range
  Set cell = Range("AK9:AR50")
  
  Application.EnableEvents = False
  If Not Application.Intersect(cell, target) Is Nothing Then
  If target.Column = 37 Then
      target.Offset(, 1).Value = target.Value / Range("V" &amp; target.Row).Value
  ElseIf target.Column = 38 Then
      target.Offset(, -1).Value = WorksheetFunction.RoundUp((target.Value * Range("V" &amp; target.Row).Value), -2)
  End If
  If target.Column = 39 Then
      target.Offset(, 1).Value = target.Value / Range("V" &amp; target.Row).Value
  ElseIf target.Column = 40 Then
      target.Offset(, -1).Value = WorksheetFunction.RoundUp((target.Value * Range("V" &amp; target.Row).Value), -2)
  End If
  If target.Column = 41 Then
      target.Offset(, 1).Value = WorksheetFunction.RoundUp((target.Value * Range("V" &amp; target.Row).Value), -2)
  ElseIf target.Column = 42 Then
      target.Offset(, -1).Value = target.Value / Range("V" &amp; target.Row).Value
  End If
  If target.Column = 43 Then
      target.Offset(, 1).Value = target.Value / Range("V" &amp; target.Row).Value
  ElseIf target.Column = 44 Then
      target.Offset(, -1).Value = WorksheetFunction.RoundUp((target.Value * Range("V" &amp; target.Row).Value), -2)
  End If
  End If
  Application.EnableEvents = True
  
  End Sub
  </code></pre>
  
  <p>Second code needed:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  Dim controlRng, nRng As Range
  Set controlRng = Range("AF9:AF1000")
  Set nRng = Intersect(controlRng, Target)
  
  If nRng Is Nothing Then Exit Sub
  
  If Target.Value = "No Promotion" Then
      Target.Offset(0, 1) = Range("M" &amp; Target.Row).Value
  ElseIf Target.Value = "Promotion" Then
      Target.Offset(0, 1) = ""
  ElseIf Target.Value = "Demotion" Then
      Target.Offset(0, 1) = ""
  ElseIf Target.Value = "Partner" Then
      Target.Offset(0, 1) = ""
  ElseIf Target.Value = "" Then
      Target.Offset(0, 1) = ""
  End If
  End Sub
  </code></pre>