Your suggestion still produces a "box" around the range "InpProd" once the
down arrow or the mouse is used. (This assumes that the user changes the
product name)
--
Wag more, bark less


"Patrick Molloy" wrote:


if its only values you need


If Target.Address = Range("InpICForm").Address Then
Range("InpProd").Value = Range("c17").Value
End If

If Target.Address = Range("InpProd").Address Then
Range("InpICForm").Value = Range("c18").Value
End If

OR

SELECT CASE Target.Address
CASE Range("InpICForm").Address
Range("InpProd").Value = Range("c17").Value
CASE Range("InpProd").Address
Range("InpICForm").Value = Range("c18").Value
END SELECT

"Brad" wrote:


Using Excel 2007 - that needs to be compatible with 2003. The problem that
I'm having working with appearance. With the macro list below - I'm still
haiving problems with copies cells being outlined - when this isn't the
desired result.

The desire result is to perform the copy and not have any ranges outlined
and have the cursor on the effective date. Just as if they simply moved the
cursor from name to date (which will occur the majority of the time)

There are four input criteria

Product name
Effective date
State

and
Form number

The user can either enter the product name or the form number and the
program needs to automatically change the other. Effective Date and State
are conditional inputs (depending on the form number)

Right now the code is as follows

Sub Worksheet_Change(ByVal Target As Range)

Dim dur As Integer
Dim Inpdate As Date
Inpdate = Range("InpEDate").Value
dur = Year(Now()) - Year(Inpdate)
If Month(Now()) < Month(Inpdate) Then
dur = dur - 1
Else
If Month(Now()) = Month(Inpdate) And Day(Now()) < Day(Inpdate) Then
dur = dur - 1
End If
End If

Application.EnableEvents = False
'Application.ScreenUpdating = False

If Target.Address = Range("InpICForm").Address Then
Range("c17").Copy
Range("InpProd").PasteSpecial xlPasteValues
' Application.CutCopyMode = False
' Range("InpEDate").Select
End If

If Target.Address = Range("InpProd").Address Then
Range("c18").Copy
Range("InpICForm").PasteSpecial xlPasteValues
' Application.CutCopyMode = False
' Range("InpEDate").Select
End If
Application.EnableEvents = True
Application.ScreenUpdating = True

Application.CutCopyMode = False
.
.
.