Help with selection(?)

[Brad]

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
..
..
..

 

[Patrick Molloy]

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]

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)

 

[Patrick Molloy]

the "boxis because the cell is selected

Range("InpEDate").Select

if you don't need this line, then delete it (there are two in your code,
none in mine)

A "feature" of excel is that the currently selected cell is highlighted. you
can get rid of thios by freezing the sheet and selectig a cell out of sight,
like AZ1000 with A1 showing in the top left

 

[Brad]

I'd agree with you but this is the new code
Application.EnableEvents = False

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
Application.EnableEvents = True
Application.ScreenUpdating = True

and I'm still getting the box....

 

[Patrick Molloy]

the 'box' presumably is the normal excel indicator of which cell is selected.
The fact that you'rr using 'Target' suggests that you've used either the
selection_change or the change event - in either case, the cell will be
highlighted by the 'box' ... thats just normal excel.

As I indicated, this 'box' will always be there unless you freeze the pane
and select a cell thats 'off screen'