VBA for the last modified date of a row

[av47ar]

Hi,

Been scratching my head at this all day, fairly new at understanding code for excel as well.

I have a sheet I use for production , a little conditional formatting for a simple RAD (red amber green) status using 1 2 3 in each cell (see attached imgur pic) https://imgur.com/5a95wjS

What I'd like to do is be able to see the last modified date for that row for reporting purposes, tried a few formulas directly in the page and they worked for a while, suddenly stopped but I think they were tracking any changes on the page.

Any suggestions/work arounds are welcome and really appreciated.

 

[av47ar]

after some fiddling im usingthis code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells(Target.Row, "AI").Value = Now()
Application.EnableEvents = True

It's monitoring all cells, is there a way to limit it to A2:Z2 and each row down to A24:Z24 (am i right saying the range of A2:Z24??)

Im using an activex button to copy some info from the first two cells to another sheet for tracking purposes and then clear its contents and fill colour. When i double click this it updates the last modified cell date as thats what the button is doing. Is there a way to have the last modified date cell to clear itself when the cells have been cleared?