I am looking for a solution.

My spreadsheet has 5 columns that i do not want this to affect. The 6th
column is "Last Updated".

When i write comments in any cell after H i want it to put the updated date
and time into "Last Updated" in that row.

Another addition to this would be to check the last updated date, if it is
upto 3days from today then turn green, upto 5days yellow and more than 7days
red!

I hope someone can help!

Hi,

generally this is very easy and the code is below but your question isn't
clear.

Is the 'Last updated' column different for every tow?

Anyway try this. Right click your sheet tab, view code and paste the code
below in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("G1:z100")) Is Nothing Then
Application.EnableEvents = False
Cells(Target.Row, 6) = Now
Application.EnableEvents = True
End If
End Sub

Mike

"BakerInSpain" wrote:

I am looking for a solution.

My spreadsheet has 5 columns that i do not want this to affect. The 6th
column is "Last Updated".

When i write comments in any cell after H i want it to put the updated date
and time into "Last Updated" in that row.

Another addition to this would be to check the last updated date, if it is
upto 3days from today then turn green, upto 5days yellow and more than 7days
red!

I hope someone can help!

The below will point you in the right direction. Select the sheet tab which
you want to work with. Right click the sheet tab and click on 'View Code'.
This will launch VBE. Paste the below code to the right blank portion. Get
back to to workbook and try out.

--Events logged for changes after column H ie from col I onwards
--upto 3 days is colored as green, upto 7 days is colored as yellow and
greater than 7 is coloured as red..You can change that to suit

Private Sub Worksheet_Activate()
lngLastRow = ActiveSheet.Cells(Rows.Count, "f").End(xlUp).Row
For lngrow = 2 To lngLastRow
If Range("F" & lngrow) < "" Then
Select Case DateDiff("d", Range("F" & lngrow), Date)
Case Is <= 3
Range("F" & lngrow).Interior.ColorIndex = 10
Case Is <= 7
Range("F" & lngrow).Interior.ColorIndex = 6
Case Else
Range("F" & lngrow).Interior.ColorIndex = 3
End Select
End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column 8 Then
Range("f" & Target.Row) = Now
Range("f" & Target.Row).Interior.ColorIndex = 10
End If
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"BakerInSpain" wrote:

I am looking for a solution.

My spreadsheet has 5 columns that i do not want this to affect. The 6th
column is "Last Updated".

When i write comments in any cell after H i want it to put the updated date
and time into "Last Updated" in that row.

Another addition to this would be to check the last updated date, if it is
upto 3days from today then turn green, upto 5days yellow and more than 7days
red!

I hope someone can help!

Right click on sheet tab

Paste this code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 8 And Target.Column <= 12 Then
CurR = Target.Row
Range("F" & CurR).Value = Now
End If

End Sub

For Coloring use the conditional formatting

to color Green - use this
=TODAY()-INT(F2)<=3

to color Yellow - use this
=AND((TODAY()-INT(F2))3,(TODAY()-INT(F2))<=5)

to color Red - use this
=TODAY()-INT(F2)7

What about if the last updated in last 6 days ?


On Nov 16, 5:01*pm, BakerInSpain
wrote:
I am looking for a solution.

My spreadsheet has 5 columns that i do not want this to affect. The 6th
column is "Last Updated".

When i write comments in any cell after H i want it to put the updated date
and time into "Last Updated" in that row.

Another addition to this would be to check the last updated date, if it is
upto 3days from today then turn green, upto 5days yellow and more than 7days
red!

I hope someone can help!