Edit for current working cell select code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,738
Office Version
  1. 2007
Platform
  1. Windows
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

Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
This code does not select any cells; it highlights the row of the selected cell when the selection changes.

WBD
 
Upvote 0
Hi,

Maybe this one then ?

Code:
Private Sub Worksheet_Activate()    Application.Goto Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp), True
    ActiveWindow.SmallScroll UP:=10
End Sub
 
Upvote 0
Yep. Just add an offset to the end.

Code:
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
 
Upvote 0
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.
 
Upvote 0
Well, the current code for highlighting only works in the range that contains data. Try changing this line:

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

To:

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

WBD
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,322
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top