M
M
MrRaden2020-07-24 19:17:45
Macros
MrRaden, 2020-07-24 19:17:45

How to set maros in Excel so that it shows data modification date in multiple columns?

5f1b0785625fb952922529.png
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


Help the teapot, plz.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
E
Eugene Kuznetsov, 2020-07-30
@KEugene

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.

B
byrew rewen, 2020-08-05
@rewen

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 question

Ask a Question

731 491 924 answers to any question