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 >=3D 8 And Target.Column <=3D 12 Then
        CurR =3D Target.Row
        Range("F" & CurR).Value =3D Now
End If

End Sub

For Coloring use the conditional formatting

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

to color Yellow - use this
=3DAND((TODAY()-INT(F2))>3,(TODAY()-INT(F2))<=3D5)

to color Red - use this
=3DTODAY()-INT(F2)>7

What about if the last updated in last 6 days ?


On Nov 16, 5:01=A0pm, BakerInSpain
<BakerInSp...@discussions.microsoft.com> 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 da=
te
> and time into "Last Updated" in that row.
>
> Another addition to this would be to check the last updated date, if it i=
s
> upto 3days from today then turn green, upto 5days yellow and more than 7d=
ays
> red!
>
> I hope someone can help!