Hi,
I have the following code for when a cell is changed the date and time and another piece of info are applied to a specfic cell.
This in principal works fine (utilising Target), however the code is more complicated due to each input row is made up of three rows (i.e merged), apart from the last three rows and columns which make up a 3x3 matrix. Hence in the code why it looks for the position of "SRT" which represents the top of the three rows. This is where the row there the date should appear, even if the 3x3 matrix is changed.
The code below seems to run for ages and when you step through it get through part of the code and then starts again?
Is there a better way of doing this and why does this code not work!??
Sorry can't attach a picture due to company restrictions.
Thanks in advance
James
'''''''''''''''''''''''''''''''
Private Sub Worksheet_Change(ByVal Target As Range)
'Applies last updated to each line
If Target.Row >= 15 And _
Target.Row <= Sheet4.Cells(6, 4) Then
If Sheet1.Cells(Target.Row, 52) = "Srt" Then
Sheet1.Cells(Target.Row, 53) = Now
Sheet1.Cells(Target.Row, 54) = Sheet4.Cells(7, 4)
ElseIf Sheet1.Cells(Target.Row - 1, 52) = "Srt" Then
Sheet1.Cells(Target.Row - 1, 53) = Now
Sheet1.Cells(Target.Row, 54) = Sheet4.Cells(7, 4)
ElseIf Sheet1.Cells(Target.Row - 2, 52) = "Srt" Then
Sheet1.Cells(Target.Row - 2, 53) = Now
Sheet1.Cells(Target.Row, 54) = Sheet4.Cells(7, 4)
End If
End If
End Sub
I have the following code for when a cell is changed the date and time and another piece of info are applied to a specfic cell.
This in principal works fine (utilising Target), however the code is more complicated due to each input row is made up of three rows (i.e merged), apart from the last three rows and columns which make up a 3x3 matrix. Hence in the code why it looks for the position of "SRT" which represents the top of the three rows. This is where the row there the date should appear, even if the 3x3 matrix is changed.
The code below seems to run for ages and when you step through it get through part of the code and then starts again?
Is there a better way of doing this and why does this code not work!??
Sorry can't attach a picture due to company restrictions.
Thanks in advance
James
'''''''''''''''''''''''''''''''
Private Sub Worksheet_Change(ByVal Target As Range)
'Applies last updated to each line
If Target.Row >= 15 And _
Target.Row <= Sheet4.Cells(6, 4) Then
If Sheet1.Cells(Target.Row, 52) = "Srt" Then
Sheet1.Cells(Target.Row, 53) = Now
Sheet1.Cells(Target.Row, 54) = Sheet4.Cells(7, 4)
ElseIf Sheet1.Cells(Target.Row - 1, 52) = "Srt" Then
Sheet1.Cells(Target.Row - 1, 53) = Now
Sheet1.Cells(Target.Row, 54) = Sheet4.Cells(7, 4)
ElseIf Sheet1.Cells(Target.Row - 2, 52) = "Srt" Then
Sheet1.Cells(Target.Row - 2, 53) = Now
Sheet1.Cells(Target.Row, 54) = Sheet4.Cells(7, 4)
End If
End If
End Sub