Match a cell in a range to a value from another sheet and select r
I have 2 worksheets A & B. I have a variable cell in sheet A which I change
from 1 - 26. I have a permanent range in a column on sheet B of 1 - 26. I need a macro that will select the row in sheet B that matches the variable in Sheet A. If I type 5 in Sheet A, the ROW with 5 on sheet B is selected. |
Match a cell in a range to a value from another sheet and selectr
Hi Johno
If you set up a worksheet change event in the sheet tab module should do the trick. This assumes your Sheet B is called Sheet2 and the cell to change is in A1 of Sheet1. You can modify to suit. Good Luck Marcus Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRw As Integer Dim rngB As Range, rngA As Range LastRw = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row Set rngA = Range("A1") Set rngB = Sheets("Sheet2").Range("A2:A" & LastRw) If Target.Address = "$A$1" Then For Each cellB In rngB Found = False For Each cellA In rngA If cellB.Value = cellA.Value Then Found = True Sheets("Sheet2").Activate cellB.EntireRow.Select End If Next Next End If End Sub |
Match a cell in a range to a value from another sheet and select r
"Johno" wrote: I have 2 worksheets A & B. I have a variable cell in sheet A which I change from 1 - 26. I have a permanent range in a column on sheet B of 1 - 26. I need a macro that will select the row in sheet B that matches the variable in Sheet A. If I type 5 in Sheet A, the ROW with 5 on sheet B is selected. Thanks Guys for your help. I ended up doing this and it worked a treat: Dim rngB As Range, rngA As Range Set rngA = Range("A1") Set rngB = Sheets("Sheet2").Range("A1:A26") For Each cellB In rngB Found = False For Each cellA In rngA If cellB.Value = cellA.Value Then Found = True Sheets("Sheet2").Activate cellB.EntireRow.Select End If Next Next End Sub |
All times are GMT +1. The time now is 08:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com