Unable to resolve small quirk in code

[Ironman]

Hi

The below code in the Training Log sheet works almost perfectly

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row + 1
Lr3 = Sheets("Indoor Bike").Range("A" & Rows.Count).End(xlUp).Row

If Target.Column = 2 And Target.Value = "OTHER" Then
    Application.EnableEvents = False
Range("A" & Target.Row).Resize(, 6).Interior.Color = RGB(197, 217, 241)
Range("I" & Target.Row).Resize(, 2).Interior.Color = RGB(197, 217, 241)
Range("I" & Target.Row).Value = "Indoor bike session, 60 mins."
 
Sheets("Indoor Bike").Range("A" & Lr2).Value = Sheets("Training Log").Range("A" & lr).Value
Sheets("Indoor Bike").Range("B" & Lr2).Value = "1:00:00"
Sheets("Indoor Bike").Range("E" & Lr2).Value = "8"
Sheets("Indoor Bike").Range("J" & Lr2).Value = "Session "
 
Range("F" & Target.Row).Select 'move to this cell to input heart rate
MsgBox "Enter Average Heart Rate", vbInformation, "Indoor Bike Session Data"
End If

Sheets("Indoor Bike").Range("F" & Lr3).Value = Sheets("Training Log").Range("F" & lr).Value  'F = Heart Rate
' jump from F to H on the same row
If Target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then   'F = Heart Rate
Range("H" & lr).Select  'H = Session rating

Sheets("Indoor Bike").Range("I" & Lr3).Value = Sheets("Training Log").Range("H" & lr).Value
   Application.EnableEvents = True
End If

For some reason though, the line below is clearing the contents of the cell in Col F in the row above it, as shown in the mini table below and I can't figure out why this is happening.

Sheets("Indoor Bike").Range("F" & Lr3).Value = Sheets("Training Log").Range("F" & lr).Value  'F = Heart Rate

Book1
	A	B	C	D	E	F	G	H	I	J
358	Wed, 29 Sep 2021	1:00:00	21.7	13.5	8		0%	160	J	Session 348. 3:00pm, Black Sabbath Live At Last (great, as ever). Started out v light and powerful, with slight angina (seems to be a trend after day's rest) until 40 mins when quads started burning and felt bit tired but good strong session overall, no idea watts output would be this high though!
359	Wed, 1 Sep 2021	1:00:00			8	111	69%		J	Session
Indoor Bike

Cell Formulas
Range	Formula
D358	D358	=IF(B358>0,C358*0.621,"")
G358:G359	G358	=F358/(220-(DATEDIF($F$1,A358,"Y")))

There is some code in the Indoor Bike sheet that affects the same row (it's basically filling in the empty columns that the above code doesn't do) but I don't think this has any bearing on the above anomaly:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long

lr = Range("A" & Rows.Count).End(xlUp).Row

If Target.Address(0, 0) = Range("C" & lr).Address(0, 0) Then
    Range("H" & lr).Select
    MsgBox "Enter Average Watts", vbInformation, "Indoor Bike Session Data"
End If

If Target.Address(0, 0) = Range("H" & lr).Address(0, 0) Then
    Range("J" & lr).Select
'Application.EnableEvents = True
End If

I'd be very grateful if this small anomaly can be resolved!

Thank you.

 

[RoryA]

Lr3 is the last row number with data in it before you add the new data.

 

[Ironman]

Thanks Rory, but the same logic applies to this row

Sheets("Indoor Bike").Range("I" & Lr3).Value = Sheets("Training Log").Range("H" & lr).Value

and the cell in the same column above it isn't cleared.

 

[RoryA]

If the cell is being cleared, then there is nothing in Sheets("Training Log").Range("F" & lr) when the code runs.

 

[Ironman]

Hmm the rows above contain data as well.

125
118

 

[RoryA]

You're assigning a value from one sheet to another. The only way the destination cell can be cleared is if the cell on the source sheet is empty at the time the code runs.