Forum Discussion

gbavikati's avatar
gbavikati
Copper Contributor
Jan 10, 2025

VBA requirement

Hello, I have requirement in VBA.

When I click on Sheet1 CellA3 then I should redirect to Sheet2 and display the Sheet1 clicked cell value and its adjacent cell value as well CellB3.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim searchValue As Variant

    Dim foundCell As Range

    Dim targetSheet As Worksheet

       If Target.Worksheet.Name = "Sheet2" Then

        If Not Intersect(Target, Target.Worksheet.Columns("A")) Is Nothing Then

            searchValue = Target.Value

            Set targetSheet = ThisWorkbook.Worksheets("Sheet1")

            Set foundCell = targetSheet.Columns("A").Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)

            If Not foundCell Is Nothing Then

                Target.Worksheet.Range("B1").Value = foundCell.Offset(0, 1).Value

            Else

                Target.Worksheet.Range("B1").Value = "No Match Found"

            End If

        End If

    End If

  • Adeelaziz's avatar
    Adeelaziz
    Brass Contributor

    Replace your VBA code with this.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Dim targetSheet As Worksheet
        Dim clickedCellValue As Variant
        Dim adjacentCellValue As Variant

        ' Check if the clicked cell is A3
        If Not Intersect(Target, Me.Range("A3")) Is Nothing Then
            ' Set the target sheet to Sheet2
            Set targetSheet = ThisWorkbook.Worksheets("Sheet2")
            
            ' Get the clicked cell value and its adjacent cell value
            clickedCellValue = Target.Value
            adjacentCellValue = Target.Offset(0, 1).Value
            
            ' Redirect to Sheet2
            targetSheet.Activate
            
            ' Display the values in Sheet2
            targetSheet.Range("A1").Value = clickedCellValue
            targetSheet.Range("B1").Value = adjacentCellValue
        End If
    End Sub




Resources