Two VBA changes not allowed??

**playtowinpb@msn.com** · 08-09-2012, 12:55 PM

Hello,

From the research I've done the VBA code won't allow these two CHANGES to happen and need to be combined. I keep getting "Ambiguous name detected: Worksheet_Change". I am a VBA noob and need assistance. Need the two codes below combined. Thank you all.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A5:A3000")) Is Nothing Then
With Target(1, 3)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub

AND

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 7 Then Exit Sub
Application.ScreenUpdating = False
Select Case Target.Value
Case "Hold"
Application.EnableEvents = False
Cells(Target.Row, 8).Value = Now
Cells(Target.Row, 8).NumberFormat = "MM/DD/YY"
Cells(Target.Row, 9).Value = ""
Application.EnableEvents = True
Case "Off Hold"
Application.EnableEvents = False
Cells(Target.Row, 9).Value = Now
Cells(Target.Row, 9).NumberFormat = "MM/DD/YY"
Cells(Target.Row, 8).Value = ""
Application.EnableEvents = True
End Select
Application.ScreenUpdating = True
End Sub

**shg** · 08-09-2012, 01:11 PM

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    On Error GoTo Oops
    Application.EnableEvents = False

    If Not Intersect(Target, Range("A5:A3000")) Is Nothing Then
        Cells(Target.Row, "C").Value = Date
        Columns("C").AutoFit
    End If

    If Target.Column = 7 Then
        Select Case Target.Value
            Case "Hold"
                Cells(Target.Row, "H").Value = Now
                Cells(Target.Row, "H").NumberFormat = "MM/DD/YY"
                Cells(Target.Row, "I").ClearContents
            Case "Off Hold"
                Cells(Target.Row, "I").Value = Now
                Cells(Target.Row, "I").NumberFormat = "MM/DD/YY"
                Cells(Target.Row, "H").ClearContents
        End Select
    End If

Oops:
    Application.EnableEvents = True
End Sub

Please use CODE tags.

**playtowinpb@msn.com** · 08-09-2012, 01:27 PM

Perfect!!!! Thank you for your help! You are a Guru.

**arlu1201** · 08-09-2012, 02:51 PM

playtowinpb@msn.com,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here