Cell Modified date and other addidtions

[JamesPTuttle]

I spent the last few days browsing through your codes. Great resource - THANKS

I have (columns) cells A ~ G with info and I update cells H ~ K

I need to track when changes are made an insert the date, time and
user name into a cell comment associated with the changed cell (H ~ K)

I also want to H ~ K to turn the cell text red after 90 days if not changed

Icing on the cake would be to allow the "date, time, username"
comment to append the cell comment instead of outright replacing it -
if the comment already exist/ or create a new one

Below are some codes taken from various archives, any in their own way, each works separately, but not together

Insert comment with now and user name below

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date in a comment when something is put in Column A created by Paul B
If Target.Column = 1 Then
Comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
Target.Cells.NoteText Comment
End If
End Sub

Turn Cell red (I would like cell text red) below

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim comment As String
With Target
If .Column = 1 Then
comment = ("Cell Last Edited: ") & Format(Date, "mm/dd/yy") & (" by ") & Application.UserName
.Cells.NoteText comment
.Interior.ColorIndex = xlNone
End If
End With
End Sub

Private Sub Workbook_Open()
Dim rng As Range, cell As Range, dte As Date
Dim ws As Worksheet
Set ws = Worksheets("Sheet1") 'Change as required
Application.ScreenUpdating = False
ws.Activate
Set rng = Intersect([A:A], ActiveSheet.UsedRange)
For Each cell In rng
On Error GoTo e
If Not cell.comment Is Nothing Then
dte = Mid(cell.NoteText, 19, 8)
If Date >= dte + 30 Then cell.Interior.ColorIndex = 3
End If
e: On Error GoTo 0
Next
End Sub


THANKS for any help

James

 

[erik.van.geit]

James,

The best way to get answers is not to ask too much at once.
Where would you like to start ? Putting one simple question to start, will provide probably some answers.

Erik

 

[PaddyD]

hi - welcome to the board!

perhaps the following will help:
http://www.mrexcel.com/board2/viewtopic.php?t=67920

 

[JamesPTuttle]

Thanks Erik & Paddy,

In short burst first . . .

How can I get the following to work. I was able to use the code provided below to get 1 cell to work but not a range of cells.

I have (columns) cells A ~ G with info and I update cells H ~ K

I need to track when changes are made an insert the date, time and
user name into a cell comment associated with the changed cell (H ~ K)


This works for 1 cell

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'will put date in a comment when something is put in Column A created by Paul B
If Target.Column = 1 Then
Comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
Target.Cells.NoteText Comment
End If
End Sub

 

[erik.van.geit]

James,

As comments are only added to the first cell of a range, you have to add them one by one (For Each...Next) This can last a while when you change a large range at once. (on my machine - 2 years old - it took 90 sec to update an entire column )

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 1 Then Exit Sub
Dim ccc As Range
Dim comment As String
Application.ScreenUpdating = False
Application.Calculation = xlManual

comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
Target.ClearComments
    For Each ccc In Target
    Range(ccc.Address).AddComment comment
    Next ccc

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

I tryed this instead of the For Each ... Next, but this calls again and again the procedure ==> slow.

Target.Cells.AddComment comment
Target.Cells(1).Copy
Target.PasteSpecial Paste:=xlPasteComments

Perhaps somebody knows a trick to speed things up ...

bye,
Erik

 

[JamesPTuttle]

Erik,

THANKS for all of the assistance.

You may be going off coarse on what I am looking to do however

I may only update 1 cell at a time - however it can be any cell between Gx to Kx


What I need is the code modified so that the comment will insert in which ever cell I modify between range Gxx to kxx

Not knowing visual, I don't know if that is hard.

eg: on Row 23, today, now, I may update a number in H23. I want the comment be inserted there. An hour from now, I may update K55. Same there - insert the comment.

Getting it to look at the range is what I don't know how to do.

THANKS

James

 

[erik.van.geit]

James,

(I'll respond you when I can, take count of the fact that I'm not al time on my computer and that I live in Belgium.)

If you want the macro working on every cell changing code was easy. Just delete one line. This line says to leave the macro if the changed cell is not in column one.

If Target.Column <> 1 Then Exit Sub 'delete this

If you never need to change more cells in one operation the code is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim ccc As Range
Dim comment As String
comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
Target.Cells.NoteText comment
End Sub

So we came back to the code you provided at first, but without the test if the target was in column one. Perhaps we turned a bit around, but anayway, this could be good to learn something, perhaps this will give you more knowledge of how macros are working.

Glad to help you,
Erik

 

[howzat]

Hi there,

I'm not sure if this alternative has been explored, or will suit your needs, but "track changes" under the tools menu will give you some of the info you need i.e, who made changes, when they were made and what the changed were.

This info appears in a cell comment, and a report of all changes made can also be generated. However, what's missing is the 90 days unedited part.

 

[JamesPTuttle]

Thanks Erik and all,

The code works

James

 

[erik.van.geit]

James,
You're welcome!
Did you manage to change the color in red ?

Putting this in a "color workbook", you will be able to chose the index of the color you like.

Sub list_colors()
Cells.Interior.ColorIndex = 15
For c = 1 To 56
Range("B" & c * 2) = c
Range("C" & c * 2).Interior.ColorIndex = c
Range("E" & c * 2) = "color " & c
With Range("E" & c * 2)
.Font.ColorIndex = c
.Interior.ColorIndex = 0
End With
Range("G" & c * 2) = "color " & c
With Range("G" & c * 2)
.Font.ColorIndex = c
.Interior.ColorIndex = 19
End With
Rows(c * 2 - 1).RowHeight = 4
Next c
End Sub

Bye,
Erik