Help to combine two Private Sub Worksheet_Changes

[Laurence D]

Hi,

I am using VBA to automate values in a specific column for a tracking sheet. It works great but as always is the case I am wanting more. Can anybody help me reorganise these 2 pieces of code so that they work simultaneously in the same worksheet change?

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)


   Dim Fnd As Range
   
   If Target.CountLarge > 3 Then Exit Sub 
   If Target.Column <> 3 Then Exit Sub 
   If Target.Cells.Count > 1 Then Exit Sub 
   If Len(Target.Value) = 0 Then Exit Sub
   
   Application.EnableEvents = False
   With Sheets("Comment List").Range("A2", Sheets("Comment List").Range("A" & Rows.Count).End(xlUp)) 
      Set Fnd = .Find(Split(Target.Value, " ")(0), , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Target.Offset(, 1).Value = Fnd.Offset(, 1).Value
      Else
         Target.Offset(, 1).Value = "Not a valid plot in this programme"
      End If
   End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


   Dim Fnd As Range
   
   If Target.CountLarge > 6 Then Exit Sub 
   If Target.Column <> 6 Then Exit Sub 
   If Target.Cells.Count > 1 Then Exit Sub 
   If Len(Target.Value) = 0 Then Exit Sub
      
Application.EnableEvents = False
   With Sheets("Comment List").Range("C2", Sheets("Comment List").Range("C" & Rows.Count).End(xlUp))
      Set Fnd = .Find(Split(Target.Value, " ")(0), , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Target.Offset(, 1).Value = Fnd.Offset(, 1).Value
      Else
         Target.Offset(, 1).Value = "Not a valid code for comments"
      End If
   End With
Application.EnableEvents = True
End Sub

Thanks Laurence

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   If Target.CountLarge > 1 Then Exit Sub
   If Len(Target.Value) = 0 Then Exit Sub

   If Target.Column = 3 Then
      Application.EnableEvents = False
      With Sheets("Comment List").Range("A2", Sheets("Comment List").Range("A" & Rows.Count).End(xlUp))
         Set Fnd = .Find(Split(Target.Value, " ")(0), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Target.Offset(, 1).Value = Fnd.Offset(, 1).Value
         Else
            Target.Offset(, 1).Value = "Not a valid plot in this programme"
         End If
      End With
   ElseIf Target.Column = 6 Then
      Application.EnableEvents = False
      With Sheets("Comment List").Range("C2", Sheets("Comment List").Range("C" & Rows.Count).End(xlUp))
         Set Fnd = .Find(Split(Target.Value, " ")(0), , , xlWhole, , , False, , False)
         If Not Fnd Is Nothing Then
            Target.Offset(, 1).Value = Fnd.Offset(, 1).Value
         Else
            Target.Offset(, 1).Value = "Not a valid code for comments"
         End If
      End With
   End If
Application.EnableEvents = True
End Sub

 

[Laurence D]

Oh great thanks Fluff! The 'With' nested within the 'If' I was trying the opposite like the noob i am! Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback