Cell Modified date and other addidtions

JamesPTuttle

New Member
Joined
Mar 19, 2004
Messages
17
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

JamesPTuttle

New Member
Joined
Mar 19, 2004
Messages
17
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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 :( )

Code:
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.
Code:
Target.Cells.AddComment comment
Target.Cells(1).Copy
Target.PasteSpecial Paste:=xlPasteComments

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

bye,
Erik
 

JamesPTuttle

New Member
Joined
Mar 19, 2004
Messages
17
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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.
Code:
If Target.Column <> 1 Then Exit Sub 'delete this

If you never need to change more cells in one operation the code is:
Code:
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

Board Regular
Joined
Oct 13, 2003
Messages
144
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top