Formula to show last activated cell in an array

[ardykav]

I have an array of cells B4:E4, I was wondering is there a formula or some code which will populate cell G4 with the last cell I have activated within the array. So once I click any of the cells in the array of B4:E4 cell G4 will change with some form of identifier, let’s say 1 for B4, 2 for C4 and so on

thanks

 

[Fluff]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:E4")) Is Nothing Then
      Range("G4") = Split(Target.Address(1, 0), "$")(0)
   End If
End Sub

This needs to go in the relevant sheet module.

 

[ardykav]

How about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:E4")) Is Nothing Then
      Range("G4") = Split(Target.Address(1, 0), "$")(0)
   End If
End Sub

This needs to go in the relevant sheet module.

Thanks thats nearly it one stupid thing I forgot, It actually needs to return the actual cell reference, it looks to be just returning the column Range, or even return a 1 for B4, 2 for C4 etc

 

[Fluff]

If you want the cell reference use

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B4:E4")) Is Nothing Then
      Range("G4") = Target.Address
   End If
End Sub

 

[ardykav]

That has worked perfectly thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ardykav]

Hi Fluff,
I made some additions to this so it took into account a lot more Cells. However I have realised that i actually now want to return the Target address - 1 row, so lets say cell AE17 is selected it returns the value AE16. does that make sense?

Here is my code now

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updates lookup values for column AV to then populate relevant selections
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("AC17:AF19")) Is Nothing Then
      Range("AV19") = Target.Address
      End If
   If Not Intersect(Target, Range("AC26:AF28")) Is Nothing Then
      Range("AV28") = Target.Address
        End If
If Not Intersect(Target, Range("AC35:AF37")) Is Nothing Then
      Range("AV37") = Target.Address
  End If
If Not Intersect(Target, Range("AC44:AF46")) Is Nothing Then
      Range("AV46") = Target.Address
  End If
   If Not Intersect(Target, Range("AC53:AF55")) Is Nothing Then
      Range("AV55") = Target.Address
  End If
       If Not Intersect(Target, Range("AC62:AF64")) Is Nothing Then
      Range("AV64") = Target.Address
  End If
      If Not Intersect(Target, Range("AC71:AF73")) Is Nothing Then
      Range("AV73") = Target.Address
  End If
      If Not Intersect(Target, Range("AC80:AF82")) Is Nothing Then
      Range("AV82") = Target.Address
  End If
      If Not Intersect(Target, Range("AC89:AF91")) Is Nothing Then
      Range("AV91") = Target.Address
  End If
End Sub

 

[ardykav]

Also does the above code not work on merged cells?

 

[Fluff]

If you use

Target.Offset(1).Address

it will give you the address of the cell above the target cell.

As for merged cells, they are an abomination & should be avoided like the plague.

 

[ardykav]

If you use

Target.Offset(1).Address

it will give you the address of the cell above the target cell.

As for merged cells, they are an abomination & should be avoided like the plague.

Thanks for the very simple solution I am clearly an idiot for not thinking that, much appreciated. And as for the merged cells yes they do not make life easy!