Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
click cell and open matching value in next sheet
I am having problems with the code below, I just can't seem to get it to work...I tried using range and cell for selection and for the then statement but kept getting errors
Private Sub Worksheet_SelectionChange(ByVal Target As Range Dim selection, val, cel selection = ActiveCell ' should be sheet1 with range e1:e20 the only cells able to cause this to star val = worksheets("sheet2").Range("e13:e200" For Each cell In va If cell = selection The ActiveCell.Activate ' should link to first cell on sheet2 with matching value and highlight the row of this cel worksheets("sheet2").Activat End I Nex End Su Any help would be greatly appreciated Thank Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
click cell and open matching value in next sheet
Is this what you're looking for ?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim TableRng As Range, C As Range Set TableRng = Sheets("Sheet2").Range("E13:E200") For Each C In TableRng C.EntireRow.Interior.ColorIndex = xlNone If C.Value = Target.Value Then Sheets("Sheet2").Activate C.EntireRow.Interior.ColorIndex = 6 End If Next End Sub Regards, Greg -----Original Message----- I am having problems with the code below, I just can't seem to get it to work...I tried using range and cell for selection and for the then statement but kept getting errors. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim selection, val, cell selection = ActiveCell ' should be sheet1 with range e1:e20 the only cells able to cause this to start val = worksheets("sheet2").Range("e13:e200") For Each cell In val If cell = selection Then ActiveCell.Activate ' should link to first cell on sheet2 with matching value and highlight the row of this cell worksheets("sheet2").Activate End If Next End Sub Any help would be greatly appreciated. Thanks Paul . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
click cell and open matching value in next sheet
Follow-up on my post:
You'll probably also want to scroll to the row that is highlighted. I include code to do this and to correct the bug if the user selects more than one cell. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim TableRng As Range, C As Range On Error Resume Next Set TableRng = Sheets("Sheet2").Range("E13:E200") For Each C In TableRng If Target.Count = 1 Then C.EntireRow.Interior.ColorIndex = xlNone If C.Value = Target.Value Then Sheets("Sheet2").Activate ActiveWindow.ScrollRow = C.Row - 5 C.EntireRow.Interior.ColorIndex = 6 End If End If Next End Sub Regards, Greg -----Original Message----- Is this what you're looking for ? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim TableRng As Range, C As Range Set TableRng = Sheets("Sheet2").Range("E13:E200") For Each C In TableRng C.EntireRow.Interior.ColorIndex = xlNone If C.Value = Target.Value Then Sheets("Sheet2").Activate C.EntireRow.Interior.ColorIndex = 6 End If Next End Sub Regards, Greg -----Original Message----- I am having problems with the code below, I just can't seem to get it to work...I tried using range and cell for selection and for the then statement but kept getting errors. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim selection, val, cell selection = ActiveCell ' should be sheet1 with range e1:e20 the only cells able to cause this to start val = worksheets("sheet2").Range("e13:e200") For Each cell In val If cell = selection Then ActiveCell.Activate ' should link to first cell on sheet2 with matching value and highlight the row of this cell worksheets("sheet2").Activate End If Next End Sub Any help would be greatly appreciated. Thanks Paul . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a click on + symbol to open a root and click on -. | Excel Discussion (Misc queries) | |||
Click on cell to open another workbook | Excel Discussion (Misc queries) | |||
click on cell takes you to specific sheet | Charts and Charting in Excel | |||
how do I format the sheet so that I may click on 1 cell at a time | Excel Worksheet Functions | |||
On click, copy cell contents to another sheet? | Excel Programming |