Hello, I am attempting to have two separate columns update with the current date when two separate and different columns are updated manually.
So far I was lead to believe that Worksheet_Change Subs in my VBA would be the solution but I cannot get two to work at the same time. I have looked a bit around here in OzGrid and a few other forums but I can't find anything that is clearly describing the solution. I'm not an expert in VBA and I don't fully understand the syntax.
Here is my code, I need to replicate it two more times to trigger from Columns E & F to Column H.
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim WorkRng As Range
- Dim Rng As Range
- Dim xOffsetColumn As Integer
- Set WorkRng = Intersect(Application.ActiveSheet.Range("A:A"), Target)
- xOffsetColumn = 6
- If Not WorkRng Is Nothing Then
- Application.EnableEvents = False
- For Each Rng In WorkRng
- If Not VBA.IsEmpty(Rng.Value) Then
- Rng.Offset(0, xOffsetColumn).Value = Now
- Rng.Offset(0, xOffsetColumn).NumberFormat = "dddd, mmm-dd (hh:mm)"
- Rng.Offset(0, xOffsetColumn).ClearContents
- End If
- Application.EnableEvents = True
- End If
- End Sub
Thanks in advance!