On 27 Jul, 16:27, Gord Dibben <gorddibbATshawDOTca wrote:
A bit cumbersome but can be done thisaway

Place these two events in the worksheet..........can have more than one if
events are different.

After you select an item from A1 dropdown just double-click on A1 to effect
the change.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set r = Range("A1") *'edit to suit the DV cell(s)
If Intersect(Target, r) Is Nothing Then
* * Exit Sub
End If
vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit
nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers
For Each rr In r
* * ival = 0
* * For i = LBound(vals) To UBound(vals)
* * * * If UCase(rr.Value) = vals(i) Then
* * * * * * ival = nums(i)
* * * * End If
* * Next
* * If ival 0 Then
* * rr.Value = ival
* * End If
* * Cancel = True
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit
Dim cell As Range
* * On Error GoTo ws_exit
* * Application.EnableEvents = False
* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * If .Value < "" Then
* * * * * * .Value = .Value * .Offset(6, 0).Value
* * * * * * End If
* * * * End With
* * End If

ws_exit:
* * Application.EnableEvents = True
End Sub

Gord

On Sun, 27 Jul 2008 00:48:32 -0700 (PDT), Pete
wrote:



I only have the one Worksheet_Change Event Sub but would like the 2
pieces of code you have done for me to be in it. Could you combine the
2 as below as the way I have it I think the first exit's before the
second has been done.- Hide quoted text -

- Show quoted text -

Thanks Gord for the time you have put in on this so far.

I take it then, that you can't just do an If Then Else depending on
the Range the Cell is currently in?

Peter