Can the last two if statements be combined together?

[Brad]

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

 

[joel]

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

 

[joel]

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.

 

[Rick Rothstein]

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.

 

[Brad]

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

 

[Rick Rothstein]

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