Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a cell in sheet1
that will then take you to a cell on sheet2 with the same value and
hightlight the row the cell is on. I can't seem to figure out how to only be able to use range e1:e20 to cause the sub to start but still allow other cells to be changed without causing the sub to start. Also i can get the active cell to match and get to sheet2, but cannot figure out how to select the matching cell on sheet2 and highlight the row the matching cell is on. This is what i have so far. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim selection, val selection = ActiveCell 'should be sheet1 range e1:e20 val = Worksheets("sheet2").Range("h1:h20") For Each cell In val If cell = selection Then Worksheets("sheet2").Activate 'should activate matching cell and highlight row End If Next End Sub Any help would be greatly appreciated. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting a cell in sheet1
Paul,
Try the event code below, copied into Sheet1's codemodule. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim varSel As Variant Dim rngVal As Range Dim myCell As Range If Target.Cells.Count < 1 Then Exit Sub varSel = ActiveCell.Value If Not Intersect(Target, Range("E1:E20")) Is Nothing Then Worksheets("Sheet2").Activate On Error GoTo NotFound: Set rngVal = Worksheets("Sheet2").Range("H1:H20").Find(varSel) rngVal.EntireRow.Select rngVal.Activate End If Exit Sub NotFound: Worksheets("Sheet1").Activate MsgBox "The value " & varSel & " was not found on Sheet2" End Sub "paul mueller" wrote in message om... that will then take you to a cell on sheet2 with the same value and hightlight the row the cell is on. I can't seem to figure out how to only be able to use range e1:e20 to cause the sub to start but still allow other cells to be changed without causing the sub to start. Also i can get the active cell to match and get to sheet2, but cannot figure out how to select the matching cell on sheet2 and highlight the row the matching cell is on. This is what i have so far. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim selection, val selection = ActiveCell 'should be sheet1 range e1:e20 val = Worksheets("sheet2").Range("h1:h20") For Each cell In val If cell = selection Then Worksheets("sheet2").Activate 'should activate matching cell and highlight row End If Next End Sub Any help would be greatly appreciated. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I make a cell content equal the tab name 'sheet1' ? | Excel Worksheet Functions | |||
jump to cell in sheet2 when values in sheet1 is true | Excel Discussion (Misc queries) | |||
Chart Title from Cell in Sheet1? | Charts and Charting in Excel | |||
[=Sheet1!A1] - if i use Column A1 ('Sheet1') / A2 ('Sheet2') | Excel Worksheet Functions | |||
How to set sheet1!cell = sheet2!samecell and set format? | Excel Programming |