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
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