Edit for current working cell select code

[ipbr21054]

Morning,
I am using the code shown below so when i open its worksheet it automatically selects the first cell in the last row which has data on.
This has worked good for me but now i wish to alter it & asking for advice please.

Say the last row with data on is Row 400 & currently when i come to this sheet the cell selected is A400
I would now like it to select the ist cell of the row without data on which would be A401

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range


    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "I"


'   *** Specify start row ***
    myStartRow = 8
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
    
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
    
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
    
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True


End Sub

 

[wideboydixon]

This code does not select any cells; it highlights the row of the selected cell when the selection changes.

WBD

 

[ipbr21054]

Hi,

Maybe this one then ?

Private Sub Worksheet_Activate()    Application.Goto Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp), True
    ActiveWindow.SmallScroll UP:=10
End Sub

 

[wideboydixon]

Yep. Just add an offset to the end.

Private Sub Worksheet_Activate()
    Application.Goto Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp).Offset(1, 0), True
    ActiveWindow.SmallScroll UP:=10
End Sub

WBD

 

[ipbr21054]

Thanks very much that worked.
To keep in line with the row being coloured blue and the selected cell green can you please which selection of the code that i first supplied can be copied & entered into this code.
Then i will have the new row blue & selected cell green and awaiting me to enter data.

 

[wideboydixon]

Well, the current code for highlighting only works in the range that contains data. Try changing this line:

'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row

To:

'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row + 1

WBD

 

[ipbr21054]

Thanks again,
It worked a treat.

Have a good Xmas.