Small tweak needed to code

[Ironman]

Hi

The following code in a worksheet_change event works, except for "jump from F to H on that same row":

Dim NextRow As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
If target.Column = 2 And target.Value = "OTHER" Then
    Range("A" & target.Row).Resize(, 6).Interior.Color = RGB(197, 217, 241)
    Range("I" & target.Row).Resize(, 2).Interior.Color = RGB(197, 217, 241)
    Application.EnableEvents = False
    Range("I" & target.Row).Value = "Indoor bike session, 60 mins."
    'select cell col F
    Range("F" & target.Row).Select
    Application.EnableEvents = True

 Application.EnableEvents = False
    MsgBox "Enter heart rate", vbInformation, "Indoor Bike Session Data"
   


 ' jump from F to H on that same row
If target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then
    Range("H" & target.Row).Select
    Application.EnableEvents = True
End If
End If

Column G contains a formula that is dependent on the Col F value input and I think that's what's stopping the final part from running, but I don't know how to fix this.

Help appreciated.

Thank you!

 

[Ironman]

Resolved

Dim NextRow As Long

lr = 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."
    Range("F" & target.Row).Select 'move to this cell to start inputting data

    MsgBox "Enter Heart Rate", vbInformation, "Indoor Bike Session Data"
End If
Application.EnableEvents = True
' jump from F to H on the same row
If target.Address(0, 0) = Range("F" & lr).Address(0, 0) Then
    Range("H" & lr).Select
End If

 

[Michael M]

Couldn't you just go to the cell..

Sub Worksheet_change(ByVal target As Range)
Dim NextRow As Long
lr = 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."
    Range("F" & target.Row).Select 'move to this cell to start inputting data
    MsgBox "Enter Heart Rate", vbInformation, "Indoor Bike Session Data"
End If
Application.EnableEvents = True
    Range("H" & lr).Select
End Sub