shades
06-16-2005, 06:24 AM
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:
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
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?
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:
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
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?