Answer the question
In order to leave comments, you need to log in
How to set maros in Excel so that it shows data modification date in multiple columns?
Hello. I'm uploading a screenshot of the file.
What is required :
So that when changing the data in the GJ (yellow) columns, the time of changing these columns is stored in the O column.
Problem :
The code contains a shift of 5 cells to the right, and the program scatters the dates anywhere, but you need what is in the "O" column - " Time"
Here is the code :
Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target 'проходим по всем измененным ячейкам
If Not Intersect(cell, Range("J4:J200, G4:G200, H4:H200, I4:I200")) Is Nothing Then 'если изменененная ячейка попадает в диапазон J4:J200
With cell.Offset(0, 5) 'вводим в пятую справа ячейку дату
.Value = Now
.EntireColumn.AutoFit 'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
End With
End If
Next cell
End Sub
Answer the question
In order to leave comments, you need to log in
So it does everything right. The offset does not go anywhere, but 5 columns to the right relative to the current cell cell. Just take the row number of this cell row and put it in Range("O"&row).Value.
Or analyze each intersection range (If Not Intersect(cell, Range("J4:J200, ...) with its own offset. For example, for J4:J200 it will be Offset(0, 5), for G4:G200 - Offset(0 , 8), etc.
Private Sub Worksheet_Change(ByVal Target As Range)
For Each cell In Target 'проходим по всем измененным ячейкам
If Not Intersect(cell, Range("J4:J200, G4:G200, H4:H200, I4:I200")) Is Nothing Then 'если изменененная ячейка попадает в диапазон J4:J200
Dim colDateBegin&, colDateEnd&
colDateBegin = 12
colDateEnd = 13
Dim intEntryRow&
intEntryRow = cell.row
With cell.Parent.Range 'вводим в пятую справа ячейку дату
.Cells(intEntryRow, colDateBegin).value = Now
.Cells(intEntryRow, colDateBegin).AutoFit 'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
.Cells(intEntryRow, colDateEnd) = Now: .Cells(intEntryRow, colDateEnd).AutoFit
End With
End If
Next cell
End Sub
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question