Changing cell information w/ macros

[ac3100]

I am trying to create a macro that will allow someone to enter information in cell#1 to have it appear in cell#2. I want to be able to have information entered in cell#2 to appear in cell #1.

I wrote this code using "Worksheet" - "Selection Change"

If ActiveCell.Row = 10 And ActiveCell.Column = 4 Then
Worksheets("Assortment").Cells(28, 3).Value = Worksheets("Summary").Cells(10, 4).Value
End If

When I enter a value in cell#1, it doesn't update until I enter something else after.

I hope this makes sense. I suppose I could write code so that it enters a formula to reference the other cell, but I was thinking there might be a cleaner way.

Thanks
Andy

 

[Damon Ostrander]

Hi Andy,

Yes, this is easy to do, but you should use the worksheet's Change event rather than the SelectionChange event. One of the things you need to do is to turn events off (EnableEvents=False) so that when your code changes the other cell it doesn't re-trigger the event and put you into an infinite recursive event loop. Here's an example with cells A1, B1 and C1, where if you enter A1, it puts A1^2 and A1^3 in cells B1 and C1, but you can also enter values in B1 and C1 and it will calculate the appropriate values for all three cells depending on which cell you enter a value into such that B1 is always A1^2 and C1 is always A1^3.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Address
Case [a1].Address
[b1] = [a1] ^ 2
[c1] = [a1] ^ 3
Case [b1].Address
[a1] = Sqr([b1])
[c1] = [b1] ^ (3 / 2)
Case [c1].Address
[a1] = [c1] ^ (1 / 3)
[b1] = [c1] ^ (2 / 3)
End Select
Application.EnableEvents = True
End Sub

 

[ac3100]

Damon,
Your solution to this problem worked beautifully!

Thank You.

Andy