hobbiton73
10-22-2012, 10:48 AM
Hi, I wonder whether someone may be able to help me please.
I'm using the code below to track and highlight changes within my workbook, changing the cell fill colour with an additional comment to show the previous value and who and when the change was made.
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
On Error Resume Next
For Each Cell In Target
If Not Intersect(Cell, Range("B5:Q2000")) Is Nothing Then
If Cell <> "" Then
Application.EnableEvents = False
With Range("A5:A" & Cell.Row)
.Value = Date
End With
With Range("AE5:AE" & Cell.Row)
.Value = "No"
End With
Application.EnableEvents = True
End If
End If
Next Cell
On Error GoTo 0
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "dd-mm-yyyy") & Chr(10) & "By " & Environ("UserName")
Target.Interior.ColorIndex = 35
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
preValue = Target.Value
End Sub
This works to some extent, but I have a few problems which I can't seem to solve.
I'm trying to set a range to be more precise B5:Q2000, whereby any changes made to the cells within that range will show the aforementioned cell colour change and comment. Despite, with my limited knowledge, incorporating this range within the code, all cells in and out of the range show the changes that have been made.
The second issue I have, is that I would only like the tracking to occur if the value of the cell is changed , rather than the current functionality which shows a cell change even if the user has just double clicked within the cell.
I've been working on these issues for quite some time now, and spent hours trying to find solutions.
I just wondered whether someone could perhaps have a look at these please and offer a little guidance on how I may go about resolving these problems.
Many thanks and the kindest regards
I'm using the code below to track and highlight changes within my workbook, changing the cell fill colour with an additional comment to show the previous value and who and when the change was made.
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
On Error Resume Next
For Each Cell In Target
If Not Intersect(Cell, Range("B5:Q2000")) Is Nothing Then
If Cell <> "" Then
Application.EnableEvents = False
With Range("A5:A" & Cell.Row)
.Value = Date
End With
With Range("AE5:AE" & Cell.Row)
.Value = "No"
End With
Application.EnableEvents = True
End If
End If
Next Cell
On Error GoTo 0
If Target.Count > 1 Then Exit Sub
Target.ClearComments
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "dd-mm-yyyy") & Chr(10) & "By " & Environ("UserName")
Target.Interior.ColorIndex = 35
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then
preValue = "a blank"
Else: preValue = Target.Value
End If
preValue = Target.Value
End Sub
This works to some extent, but I have a few problems which I can't seem to solve.
I'm trying to set a range to be more precise B5:Q2000, whereby any changes made to the cells within that range will show the aforementioned cell colour change and comment. Despite, with my limited knowledge, incorporating this range within the code, all cells in and out of the range show the changes that have been made.
The second issue I have, is that I would only like the tracking to occur if the value of the cell is changed , rather than the current functionality which shows a cell change even if the user has just double clicked within the cell.
I've been working on these issues for quite some time now, and spent hours trying to find solutions.
I just wondered whether someone could perhaps have a look at these please and offer a little guidance on how I may go about resolving these problems.
Many thanks and the kindest regards