Update a cell value with Row and Column number for

Question

Hi I have a spreadsheet similar to below

Where when I click on a cell (red cell), I want to return the row and column number to another cell for use in an indirect lookup (blue cell)

Ideally I want to only update the cell value if it's within a set range or at least limit it only to that worksheet for error handling.

Hope that's clear... not an easy thing to google. My experiments with

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    MsgBox ActiveCell.Row
End Sub

Have returned nothing, not even a message box even though macros run fine. Any ideas?

Answer 1

Use this in the worksheet's private code sheet.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target.Cells(1), Range("C4:H9")) Is Nothing Then
        Range("C4:H9").Interior.Pattern = xlNone
        Cells(3, "J") = Join(Array(Cells(Target.Cells(1).Row, "B"), _
                                   Cells(3, Target.Cells(1).Column)), Chr(44))
        Target.Cells(1).Interior.ColorIndex = 3
    End If
End Sub

Answer 2

Based on your example. Make sure your code is in the appropriate sheet module, not a standard module and make sure Application.EnableEvents=True (your existing code should have done something).

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Intersect(Target(1), Range("C4:H9")) Is Nothing Then Exit Sub

Range("J3").Value = Cells(Target(1).Row, 2) & "," & Cells(3, Target(1).Column)

End Sub