Postion view

[Martin]

Hi,

I have the following code which works perfectly. It finds the users log in
name from a range of cells and highlights the cells, in this case 30 cells in
a single row.

The problem is I want to position the first cell (column 118) just left of
the first column not frozen (column I on my sheet). At the moment it selects
the cells but part of them are off the screen and you need to scroll across.

I know I can use a scroll to move to the correct postion as below:
(ActiveWindow.SmallScroll ToRight:=14)

but this depends on the screen resolution, on other PCs the scroll is too
much, on others its too little.

Is there a way to position the first cell left of the first frozen pane?

I woudl really like to crack this one, any help is very much appreciated.

Martin

Sub GoToJun()

Dim lRow As Long
Dim lCol As Long

For lRow = 34 To 100
If Cells(lRow, 2).Value = Environ("Username") Then
Range(Cells(lRow, 118), Cells(lRow, 139)).Select
End If

Next

End Sub

 

[Nigel]

Check out the Application.Goto method. It uses two parts

The reference to the range
A scroll option (boolean) so it appears in the upper left corner of the
window if true.

 

[Martin]

Nigel, many thanks, this works perfectly. Very much appreciated.

Here is the amended code:

Sub GoToJun()

Dim lRow As Long
Dim lCol As Long

For lRow = 34 To 100
If Cells(lRow, 2).Value = Environ("Username") Then
Application.Goto Reference:=Range(Cells(lRow, 118), Cells(lRow, 139)),
scroll:=True
End If

Next

End Sub

Martin