Adaptation of existing vba code to "understand" txt/date format
Posted on 2011-09-19
I'm looking forward to receive your help to adapt some existing vba code or suggest another new solution:
The code below works fine and helps me to track changes made in the defined range.
However, in that range I'm using different formats. I have limited them to txt and date.
Depending on the cell changed the receiving cell must understand that this is txt or date format. Text format is ok as result but date is not shown in a readable format.
This formula would solve the issue but in this case I would need a helping column. Better would be of course if possible to add this to the existing coding.
For example =IF(ISNUMBER(E8)=FALSE;E8;TEXT(E8;"dd-mmm-yyyy"))
THIS IS THE CODE I'M CURRENTLY USING:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("A5:AE100")
Sheets("Tracking").Protect Password:="123456", DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
Sheets("Tracking").EnableAutoFilter = True
If Not Intersect(Target, rng) Is Nothing Then
Target.Offset(0, 32 - Target.Column).Value = Now()
Target.Offset(0, 33 - Target.Column).Value = Environ("UserName")
'Check empty cell
If Intersect(Target, Range("A5:AE100")) Is Nothing Then Exit Sub
With Sheets("Tracking").Range("A" & Rows.Count).End(xlUp)(2)
.Value = Now
.Offset(, 1).Value = Environ("username")
.Offset(, 2).Value = Target.Address
.Offset(, 3).Value = vOld
.Offset(, 4).Value = Target
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Save old value
If Not Intersect(Target, Range("A4:AE100")) Is Nothing Then vOld = Target