Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "2:20" '<== change to suit
Dim ans, aryOptions
Dim i As Long
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
ans = InputBox("What colours?")
If ans = False Then Exit Sub
aryOptions = Split(ans, ",")
Target.Offset(1, 0).Resize(UBound(aryOptions) -
LBound(aryOptions)).EntireRow.Insert
Target.EntireRow.AutoFill Target.Resize(UBound(aryOptions) -
LBound(aryOptions) + 1).EntireRow
For i = LBound(aryOptions) To UBound(aryOptions)
Me.Cells(Target.Offset(i, 0).Row, "A").Value =
Me.Cells(Target.Offset(i, 0).Row, "A").Value & "\" & aryOptions(i)
Me.Cells(Target.Offset(i, 0).Row, "D").Value =
Me.Cells(Target.Row, "D").Value
Next i
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Materialised" wrote in message
...
Hi,
Thank you to all who replies to my previous post. It seems I had
misunderstood the problem.

(Going back to my BMX bike example)
We have the following data:

ProdID Desc Price
2374 BMX Bicycle £99.99

Now this bike comes in various colours (Red Green and Blue), so what I
want to be able to do, is slect the entire row, and run a macro. Which
would prompt the user to specify what colours the product was available
in. So for this example, the user wouldd type "R,G,B".
Which would then replace to current line, with the following:

ProdID Desc Price
2374/R BMX Bicycle £99.99
2374/G BMX Bicycle £99.99
2374/B BMX Bicycle £99.99

Does anyone know how I would acomplish this?

Kind Regards