If the user change c4, i want the value of c39 to change or
If the user change e4, i want the value of e39 to change

Is there a better way??

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Range("c4", "e4"), Target) Is Nothing Then
Application.EnableAutoComplete = True
Else
Application.EnableAutoComplete = False
End If
If Intersect(Range("c4"), Target) Is Nothing Then
Application.EnableAutoComplete = True
Else
Range("c39").Value = Range("c4").Value
End If
If Intersect(Range("e4"), Target) Is Nothing Then
Application.EnableAutoComplete = True
Else
Range("e39").Value = Range("e4").Value
End If

End Sub

This will work. It uses binary arithmetic.

Sub Worksheet_SelectionChange(ByVal Target As Range)
Results = 0
if not Intersect(Range("c4"), Target) is nothing then
Results = Results + 1
end if
if not Intersect(Range("E4"), Target) is nothing then
Results = Results + 2
end if

select Case Results
case 0:
Application.EnableAutoComplete = True

case 1:
Application.EnableAutoComplete = True
Range("c39").Value = Range("c4").Value

case 2:
Application.EnableAutoComplete = True
Range("e39").Value = Range("e4").Value

case 3:
Application.EnableAutoComplete = False
Range("c39").Value = Range("c4").Value
Range("e39").Value = Range("e4").Value
end select
end sub


"Brad" wrote:

> If the user change c4, i want the value of c39 to change or
> If the user change e4, i want the value of e39 to change
>
> Is there a better way??
>
> Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Intersect(Range("c4", "e4"), Target) Is Nothing Then
> Application.EnableAutoComplete = True
> Else
> Application.EnableAutoComplete = False
> End If
> If Intersect(Range("c4"), Target) Is Nothing Then
> Application.EnableAutoComplete = True
> Else
> Range("c39").Value = Range("c4").Value
> End If
> If Intersect(Range("e4"), Target) Is Nothing Then
> Application.EnableAutoComplete = True
> Else
> Range("e39").Value = Range("e4").Value
> End If
>
> End Sub
>

Brad: I forgot to mention one item. if somebody copies a range of cells and
pastes it on over both cells, both cells will change.

"joel" wrote:

> This will work. It uses binary arithmetic.
>
> Sub Worksheet_SelectionChange(ByVal Target As Range)
> Results = 0
> if not Intersect(Range("c4"), Target) is nothing then
> Results = Results + 1
> end if
> if not Intersect(Range("E4"), Target) is nothing then
> Results = Results + 2
> end if
>
> select Case Results
> case 0:
> Application.EnableAutoComplete = True
>
> case 1:
> Application.EnableAutoComplete = True
> Range("c39").Value = Range("c4").Value
>
> case 2:
> Application.EnableAutoComplete = True
> Range("e39").Value = Range("e4").Value
>
> case 3:
> Application.EnableAutoComplete = False
> Range("c39").Value = Range("c4").Value
> Range("e39").Value = Range("e4").Value
> end select
> end sub
>
>
> "Brad" wrote:
>
> > If the user change c4, i want the value of c39 to change or
> > If the user change e4, i want the value of e39 to change
> >
> > Is there a better way??
> >
> > Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Intersect(Range("c4", "e4"), Target) Is Nothing Then
> > Application.EnableAutoComplete = True
> > Else
> > Application.EnableAutoComplete = False
> > End If
> > If Intersect(Range("c4"), Target) Is Nothing Then
> > Application.EnableAutoComplete = True
> > Else
> > Range("c39").Value = Range("c4").Value
> > End If
> > If Intersect(Range("e4"), Target) Is Nothing Then
> > Application.EnableAutoComplete = True
> > Else
> > Range("e39").Value = Range("e4").Value
> > End If
> >
> > End Sub
> >

If I read what you are doing correctly, you should be able to use this code
in place of what you posted...

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("C4,E4"), Target) Is Nothing Then
Application.EnableAutoComplete = False
Cells(39, Target.Column).Value = Target.Value
Application.EnableAutoComplete = True
End If
End Sub

Notice that I changed the Range you used in the Intersect function call from
("C4","E4") to ("C4,E4")... the range you used included the cells C4, D4 and
E4 whereas your subsequent code seemed to indicate that you don't care about
Target being D4.

--
Rick (MVP - Excel)


"Brad" <(E-Mail Removed)> wrote in message
news:B2398E46-7EC3-4AB1-9F00-(E-Mail Removed)...
> If the user change c4, i want the value of c39 to change or
> If the user change e4, i want the value of e39 to change
>
> Is there a better way??
>
> Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Intersect(Range("c4", "e4"), Target) Is Nothing Then
> Application.EnableAutoComplete = True
> Else
> Application.EnableAutoComplete = False
> End If
> If Intersect(Range("c4"), Target) Is Nothing Then
> Application.EnableAutoComplete = True
> Else
> Range("c39").Value = Range("c4").Value
> End If
> If Intersect(Range("e4"), Target) Is Nothing Then
> Application.EnableAutoComplete = True
> Else
> Range("e39").Value = Range("e4").Value
> End If
>
> End Sub
>

For case 1 and 2 should not the application.EnableAutoComplete be False?

"joel" wrote:

> This will work. It uses binary arithmetic.
>
> Sub Worksheet_SelectionChange(ByVal Target As Range)
> Results = 0
> if not Intersect(Range("c4"), Target) is nothing then
> Results = Results + 1
> end if
> if not Intersect(Range("E4"), Target) is nothing then
> Results = Results + 2
> end if
>
> select Case Results
> case 0:
> Application.EnableAutoComplete = True
>
> case 1:
> Application.EnableAutoComplete = True
> Range("c39").Value = Range("c4").Value
>
> case 2:
> Application.EnableAutoComplete = True
> Range("e39").Value = Range("e4").Value
>
> case 3:
> Application.EnableAutoComplete = False
> Range("c39").Value = Range("c4").Value
> Range("e39").Value = Range("e4").Value
> end select
> end sub
>
>
> "Brad" wrote:
>
> > If the user change c4, i want the value of c39 to change or
> > If the user change e4, i want the value of e39 to change
> >
> > Is there a better way??
> >
> > Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Intersect(Range("c4", "e4"), Target) Is Nothing Then
> > Application.EnableAutoComplete = True
> > Else
> > Application.EnableAutoComplete = False
> > End If
> > If Intersect(Range("c4"), Target) Is Nothing Then
> > Application.EnableAutoComplete = True
> > Else
> > Range("c39").Value = Range("c4").Value
> > End If
> > If Intersect(Range("e4"), Target) Is Nothing Then
> > Application.EnableAutoComplete = True
> > Else
> > Range("e39").Value = Range("e4").Value
> > End If
> >
> > End Sub
> >

> Cells(39, Target.Column).Value = Target.Value

You could also use this line instead of the one above (they will both work
the same)...

Target.Offset(35).Value = Target.Value

--
Rick (MVP - Excel)


"Rick Rothstein" <(E-Mail Removed)> wrote in message
news:OYu$(E-Mail Removed)...
> If I read what you are doing correctly, you should be able to use this
> code in place of what you posted...
>
> Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Intersect(Range("C4,E4"), Target) Is Nothing Then
> Application.EnableAutoComplete = False
> Cells(39, Target.Column).Value = Target.Value
> Application.EnableAutoComplete = True
> End If
> End Sub
>
> Notice that I changed the Range you used in the Intersect function call
> from ("C4","E4") to ("C4,E4")... the range you used included the cells C4,
> D4 and E4 whereas your subsequent code seemed to indicate that you don't
> care about Target being D4.
>
> --
> Rick (MVP - Excel)
>
>
> "Brad" <(E-Mail Removed)> wrote in message
> news:B2398E46-7EC3-4AB1-9F00-(E-Mail Removed)...
>> If the user change c4, i want the value of c39 to change or
>> If the user change e4, i want the value of e39 to change
>>
>> Is there a better way??
>>
>> Sub Worksheet_SelectionChange(ByVal Target As Range)
>> If Intersect(Range("c4", "e4"), Target) Is Nothing Then
>> Application.EnableAutoComplete = True
>> Else
>> Application.EnableAutoComplete = False
>> End If
>> If Intersect(Range("c4"), Target) Is Nothing Then
>> Application.EnableAutoComplete = True
>> Else
>> Range("c39").Value = Range("c4").Value
>> End If
>> If Intersect(Range("e4"), Target) Is Nothing Then
>> Application.EnableAutoComplete = True
>> Else
>> Range("e39").Value = Range("e4").Value
>> End If
>>
>> End Sub
>>
>