Help on Worksheet Change

[ilvmgicker]

Forget the values I have used in the simple example, they are there just to
show you what I need to work.

Using the code...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G1")) Is Nothing Then

If Range("G1").Value = Range("B3").Value Then
Range("A3").Value = Range("A1").Value
Range("C3").Value = Range("C1").Value
Range("E3:F3").Value = Range("E1:F3").Value

End If
End If
End Sub

If I enter ton in G1 the values in the cells change.


Before Worksheet Change

A B C D E F G
1 gun pig cat pen too hit
2
3 ton

I enter ton in cell G1

After Worksheet Change

A B C D E F G
1 gun pig cat pen too hit ton
2
3 gun ton cat too hit


With me? This works fine for a small project, but I have many values to
check.
Suppose Range A1:G35 (change If Not Intersect(Target, Range("G1:G35")) Is
Nothing Then) holds all the data I need to 'copy' if there is a match and
Range A71:F105 is where those 'copied' values are placed.
One problem is the code gets very long, repetitive and finally the code gets
too big for EXcel and it won't run.

Is there anyway I can shorten the code? Got an idea it has something to do
with variables but I am new to VBA and am getting really lost.

Thanks in advance

 

[ilvmgicker]

Sorry, the before worksheet change should have the value ton in B3 not A3.