Maybe challenging to explain, i'll try-
I have two Sheets. The active sheet is sheet 1, and that's where I need folks to change "comments". I need the date of those changes recorded on the very same cell number, plus 1 (so offset of 0,1) but on sheet2.
In other words, two sheets- one called "Main", the other called "Info."
Users stay on "Main" and update comments in the cells of column K about the status of listed projects. On sheet "Info," I want those comments to appear in "info" column K, and the dates to appear in "info" column L. BUT- I need sheet "Main" to be the active sheet at all times.
Possible? I'm stumped, hopefully it's a matter of a command line or something that I have never encountered. I'm VERY new to VB.
Here is the code that puts the date in the right positions on "MAIN", but I need it to be on "INFO" and not MAIN.
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("K:K"), Target)
xOffsetColumn = 1
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 = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
Set WorkRng = Intersect(Application.ActiveSheet.Range("G:G"), Target)
xOffsetColumn = 5
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 = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub
So can I just some change the address at the lines that read :
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
If so, how???
Argh!
Thanks for anyone reading this far.
