Click cell - move to that address

This setup works well in XL2003 (Win 2000). However, the VBA code does not work in XL2004 (Mac OS X 10.3.9).

List in column B

in cell D2,

=MAX($B$1:$B$100)

in cell E2 is this formula:

=ADDRESS(ROW(Data)+MATCH($D$2,Data,0)-1,COLUMN(Data))

Cell E2 then gives the address of the MAX in column B. As values change in column B, so would the MAX and thus the cell reference. The goal is to click on cell E2 and have the cursor move to whatever cell reference is listed in cell E2.

Here is the code for XL 2003:

[vba]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myAddr As String
If Target = Range("E2") Then
myAddr = Range("E2").Value
Range(myAddr).Activate
End If
End Sub
[/vba]

Unfortunately I can't do any further testing on Mac for a couple of days. Can anyone spot the problem that would not work in XL 2004 or XL X?

Works fine in Excel X (OS 10.3.9).

What kind of behavior are you seeing?

Thanks for testing.


That's the strange thing. It isn't doing anything. When I change a value in Column B, the formulas in D2 and E2 change. However, when I click on cell E2, it clicks in the cell itself - like it had been double-clicked for editing.

I will be able to test it tonight and see what else I can find.

Well, duh! I posted the correct code, but sent the previous revision to my house to use - which had the code wrong. No wonder it didn't work. I sent home this latest - and it worked exactly as intended.

Ya know, it really helps to read directions - and follow them! ;)

Hehe! lol ! Well, it happens to the best of us... :rotlaugh: