Filling shape/cell with text dependent on which cell is highlighted

[Cave_Johnson]

Hi all,

What I'm trying to do is fill a shape or cell (whichever is easiest to code!) with specific text depending on which cell is selected. The text is located on a different sheet in the same workbook. I'd also like a default text to be in there if any other cell is selected.

It'll end up being a lot more detailed than this (I think there will be 20+ options), but if I can get the grammar right then I should be able to fill out the rest.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Range("B4:K4"), Target) Is Nothing Then
Sheets("Definitions").Range("B2").Copy
Sheets("Target").Activate
Range("M10"). Select
ActiveSheet.Paste
Application.CutCopyMode = False

ElseIf Not Intersect(Range("B5:K5"), Target) Is Nothing Then
Sheets("Definitions").Range("C2").Copy
Sheets("Target").Activate
Range("M10"). Select
ActiveSheet.Paste
Application.CutCopyMode = False

Else 
Sheets("Definitions").Range("A2").Copy
Sheets("Target").Activate
Range("M10"). Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

What's wrong with that!?

 

[Fluff]

What sheet is the code in?

 

[Cave_Johnson]

What sheet is the code in?

In the Target sheet

 

[Fluff]

Ok, how about

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Range("B4:K4"), Target) Is Nothing Then
      Range("M10").Value = Sheets("Definitions").Range("B2").Value
   ElseIf Not Intersect(Range("B5:K5"), Target) Is Nothing Then
      Range("M10").Value = Sheets("Definitions").Range("C2").Value
   Else
      Range("M10").Value = Sheets("Definitions").Range("A2").Value
   End If
End Sub

 

[Cave_Johnson]

I think that is great :D Thanks! Will fill in all the details now.

One last question - is there a simple way I can get it to copy the formatting as well?

 

[Fluff]

Yup, just use it like

      Sheets("Definitions").Range("B2").Copy Range("M10")