CODEWITHSUNDEEP
excelvba
Rehaan
Rehaan

Reputation: 1

Cell Selected should display message which is on a cell on another sheet

I am new to Excel VBA so please be patient with me. Thanks.

I have 2 sheets in my workbook. Sheet1 and Sheet2 both has same number of rows. I have a column named as SITE in Sheet2 and REMARK named on Sheet1. What I need is whenever I select the cell in SITE column ,it should display a message(i.e; message should contain cell value of REMARK column) from the Sheet1.

For example: If SITE column-cell 3 is selected then message displayed should contain value of REMARK column-cell 3.

Upvotes: 0

Views: 284

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

because I'm bored:

'in Sheet1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = Application.Match("SITE", Target.Parent.Rows(1), 0) Then MsgBox Sheets("Sheet2").Cells(Target.Row, Application.Match("REMARK", Sheets("Sheet2").Rows(1), 0)).Value2
End Sub

'in Sheet2
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = Application.Match("REMARK", Target.Parent.Rows(1), 0) Then MsgBox Sheets("Sheet1").Cells(Target.Row, Application.Match("SITE", Sheets("Sheet1").Rows(1), 0)).Value2
End Sub

EDIT
your "...SITE column..." in the question said me that the headers are at row 1... but if you know where they are, and the pos is not changing, then you can use it like this (I assume that the first value for "SITE" is in P8 and the first "REMARK" at Q9 to show how to offset):

'in Sheet1
'the "Target.Row + 1" need the row offset because it starts 1 row later
'if they start at the same row, the "+ 1" can be deleted
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = Range("P1").Column And Target.Row >= 8 Then MsgBox Sheets("Sheet2").Range("Q" & Target.Row + 1).Value2
End Sub

'---------------------------------------------------------------------

'in Sheet2
'because the offset must be the opposite, the "+ 1" becomes "- 1" here
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Column = Range("Q1").Column And Target.Row >= 9 Then MsgBox Sheets("Sheet1").Range("P" & Target.Row - 1).Value2
End Sub

Upvotes: 2

Related Questions