Selection.End(xldown).Select variable number of times

[Will85]

In column A, I have strategically placed the value of x in specific rows.

I use a macro that goes to column A and then executes selection.end(xldown).Select a certain number of times depending on which row I want it to land on.

For example if I want it to select the third row with an x I use:

Range("A1").Select
selection.end(xldown).Select
selection.end(xldown).Select
selection.end(xldown).Select


I would prefer to be able to tell it to go down a specific # of times based on the value in a cell on another sheet.

Additionally, I need it to look up this value, instead of pointing to a specific cell reference.

For example, Id want to index match the value based on an account number, the account number its looking up can be hardcoded inside the macro itself.

Sheet2 has account numbers in column A and the value I am seeking in column B.

 

[Fluff]

You will need to record a macro protecting the sheet with the parameters you want & then use that in the code I supplied, but add the userinterfaceonly at the end.

 

[Will85]

Its still doing this weird thing where its allowing me to type in the unprotected cells, but the actual highlighted outline of the active cells has disappeared, so now users dont know if the cell they clicked on has been activated or not. I dont know if I am describing correctly, but the basic function of clicking a cell and getting the darker border around the active cell, that has disappeared.

I tried this, which is resulting in the issue:

Sub x()
Dim Shp As Shape
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowDeletingRows:=True, userinterfaceonly:=True
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
.Offset(-1).Copy
.Offset(-1).Insert xlDown
.Offset(-2).Locked = False
.FormulaHidden = False
.Offset(-2).RowHeight = 50
End With
End Sub


So now I am using this, which does not result in the error. i think there is something missing when using the userinterfaceonly code

This works:

Sub x2()
ActiveSheet.Unprotect
Dim Shp As Shape
Set Shp = ActiveSheet.Shapes(Application.Caller)
With Shp.TopLeftCell.EntireRow
.Offset(-1).Copy
.Offset(-1).Insert xlDown
.Offset(-2).Locked = False
.FormulaHidden = False
.Offset(-2).RowHeight = 50
End With
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowDeletingRows:=True
End Sub

You will need to record a macro protecting the sheet with the parameters you want & then use that in the code I supplied, but add the userinterfaceonly at the end.

 

[Fluff]

If the 2nd code works, then use it.
Why you're not getting the outline around the active cell I don't know, as I don't get that