gregtgonzalez
New Member
- Joined
- Dec 16, 2016
- Messages
- 29
Hello All!
I am working on some macros, one i have is triggered when a capital "X" is entered into any cell in a particular column, it then adds the current date right next to it. I had help writing this code, however, I want to alter the macro so that when something populates column "A:A" that column AD populates the date it was entered.
I.e. Assignment is entered into column A on 1/11/2018 and because the column has value input then it will time stamp to show when it was assigned in column AD. I cant seem to get this to work in conjunction with the code below. When the "X" is entered in U:W:Y it does correctly make the time stamp but i cant seem to get them both to wokr at the same time.
Any help would be appreciated!
Private Sub Worksheet_Change_Seven_offset(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("U:U, W:W, Y:Y"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell = "X" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YY"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
altered code
Private Sub Worksheet_Change_Seven_offset_Assignment(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell = "<>" Then
With rCell.Offset(0, 29)
.Value = Now
.NumberFormat = "MM/DD/YY"
End With
Else
rCell.Offset(0, 29).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
End With
Else
rCell.Offset(0, 29).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
I am working on some macros, one i have is triggered when a capital "X" is entered into any cell in a particular column, it then adds the current date right next to it. I had help writing this code, however, I want to alter the macro so that when something populates column "A:A" that column AD populates the date it was entered.
I.e. Assignment is entered into column A on 1/11/2018 and because the column has value input then it will time stamp to show when it was assigned in column AD. I cant seem to get this to work in conjunction with the code below. When the "X" is entered in U:W:Y it does correctly make the time stamp but i cant seem to get them both to wokr at the same time.
Any help would be appreciated!
Private Sub Worksheet_Change_Seven_offset(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("U:U, W:W, Y:Y"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell = "X" Then
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YY"
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
altered code
Private Sub Worksheet_Change_Seven_offset_Assignment(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("A:A"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell = "<>" Then
With rCell.Offset(0, 29)
.Value = Now
.NumberFormat = "MM/DD/YY"
End With
Else
rCell.Offset(0, 29).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
End With
Else
rCell.Offset(0, 29).Clear
End If
Next
End If
ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Last edited: