macro to search a particular value in another sheet on double click of a cell value

See more:

VBA

Hi,

In a workbook there are 2 sheets, if i click or double click on a particular value present in column A, then that value should be searched in sheet 2 and after the match is found then sheet 2 should be opened and that particular cell where value is present should be highlighted.


This is what i have done. The code works only for 1st value but i guess some for loop issue because of which it is not working for other values. Code is:

Sub findvalue()
Dim a As String
Dim b As String
Dim someSheet As Worksheet
Dim i As Integer
Dim cell As Range

a = ActiveCell.Value


Dim LastRow As Long
With ActiveWorkbook.Sheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox LastRow

For i = 2 To LastRow


b = Sheet2.Range("A" & i).Value

If a = b Then

Set someSheet = ActiveWorkbook.Sheets("Sheet2")
someSheet.Activate

ActiveWorkbook.Sheets("Sheet2").Activate

someSheet.Range("A" & i).Select

End If
Exit For

Next i

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then

findvalue
End If
End Sub


Regards,
Archies