How to combine two Private Sub Worksheet_Change for one sheet

[WITRONNN]

Hello, I'm looking for two Private Sub Worksheet_Change for one sheet.
I need these to both to work, but can only get the first one to work.
Is there a way to get these to work by combining them somehow?

Private Sub Worksheet_Change(ByVal Target As Range)
' Auto Date
Dim Cell As Range
For Each Cell In Target
If Cell.Column = Range("I:I").Column Then
If Cell.Value <> "" Then
Cells(Cell.Row, "D").Value = Now
Else
Cells(Cell.Row, "D").Value = ""
End If
End If
Next Cell
End Sub


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Address <> "$A$1" Then Exit Sub
Set rng = Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole)
If rng.Address = Target.Address Then
MsgBox "Not found!", vbOKOnly, "Search for '" & Target.Value & "'"
Exit Sub
End If
rng.Activate
End Sub

 

[Joe4]

Welcome to the Board!

Just stack them, one over the other in a single Worksheet_Change procedure.
Also note, when updating cells within a single Worksheet_Change procedure, it is best to temporarily disable events from firing while making cell updates, or else the changes in the code will trigger another change and another round to run (people have gotten caught in infinite loops this way!).

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

'***FIRST PROCEDURE***
'   Auto Date
    Dim Cell As Range
    For Each Cell In Target
        If Cell.Column = Range("I:I").Column Then
            Application.EnableEvents = False
            If Cell.Value <> "" Then
                Cells(Cell.Row, "D").Value = Now
            Else
                Cells(Cell.Row, "D").Value = ""
            End If
            Application.EnableEvents = True
        End If
    Next Cell
 
'***SECOND PROCEDURE***
    Dim rng As Range
    If Target.Address <> "$A$1" Then Exit Sub
        Set rng = Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole)
        If rng.Address = Target.Address Then
            MsgBox "Not found!", vbOKOnly, "Search for '" & Target.Value & "'"
        Exit Sub
    End If
    rng.Activate
   
End Sub

Also, when posting VBA code in the future, please use Code tags as shown here: How to Post Your VBA Code
It makes your code much easier to read and work with!

 

[WITRONNN]

Welcome to the Board!

Just stack them, one over the other in a single Worksheet_Change procedure.
Also note, when updating cells within a single Worksheet_Change procedure, it is best to temporarily disable events from firing while making cell updates, or else the changes in the code will trigger another change and another round to run (people have gotten caught in infinite loops this way!).

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

'***FIRST PROCEDURE***
'   Auto Date
    Dim Cell As Range
    For Each Cell In Target
        If Cell.Column = Range("I:I").Column Then
            Application.EnableEvents = False
            If Cell.Value <> "" Then
                Cells(Cell.Row, "D").Value = Now
            Else
                Cells(Cell.Row, "D").Value = ""
            End If
            Application.EnableEvents = True
        End If
    Next Cell
 
'***SECOND PROCEDURE***
    Dim rng As Range
    If Target.Address <> "$A$1" Then Exit Sub
        Set rng = Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole)
        If rng.Address = Target.Address Then
            MsgBox "Not found!", vbOKOnly, "Search for '" & Target.Value & "'"
        Exit Sub
    End If
    rng.Activate
  
End Sub

Also, when posting VBA code in the future, please use Code tags as shown here: How to Post Your VBA Code
It makes your code much easier to read and work with!

Thank you!
This worked perfectly.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

How to combine two Private Sub Worksheet_Change for one sheet [SOLVED]

Hello, I'm looking for two Private Sub Worksheet_Change for one sheet. I need these to both to work, but can only get the first one to work. Is there a way to get these to work by combining them somehow? Private Sub Worksheet_Change(ByVal Target As Range) ' Auto Date Dim Cell As Range...

www.excelforum.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jues]

funcionará para tres listas desplegables (A, B, C) donde cambiar A anula B y C y cambiar B anula C

 

[Joe4]

funcionará para tres listas desplegables (A, B, C) donde cambiar A anula B y C y cambiar B anula C

This reply seems out of place. It seems to have nothing to do with the question, and is in a different language.
If you have a question, please post it to its own thread.
And if you would like to post in a language other than English, please post it here: Questions in Other Languages

 

[jues]

Sorry for the language error, it will work for three dropdown lists (D13, G13, I13) where changing the D13 list will clear the G13 and I13 lists and changing the G13 list will clear the I13 list try this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("D13").Address Then
Range("G13").Value = ""
End If


but when executing it tells me that there is an ambiguous name

 

[Joe4]

Sorry for the language error, it will work for three dropdown lists (D13, G13, I13) where changing the D13 list will clear the G13 and I13 lists and changing the G13 list will clear the I13 list try this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("D13").Address Then
Range("G13").Value = ""
End If


but when executing it tells me that there is an ambiguous name

I am not quite clear on if you are asking a question about this particular thread, if you have your own new question (in which case you should post it in a new thread), or you are trying to add assistance to this already solved question.