This is very cool JE... I'm using this as a learning tool, and I'm just
wondering if there is any reason that you used:

If Target.Column = .Offset(1, 4).Column Then
rather than:
If Target.Column = .Offset(0, 4).Column Then

I'm guessing that since you are only interested in the column at this point,
it doesn't really matter what you offset the rows by - but I've guessed
wrong before!

Thanks,

Patti



"JE McGimpsey" wrote in message
...
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
With Cells(.Row, 1).Resize(1, 5)
If Not Intersect(Target, .Cells) Is Nothing Then
Application.EnableEvents = False
If Target.Column = .Offset(1, 4).Column Then
.Resize(1, 4).Value = Target.Value / 4
Else
.Offset(0, 4).Resize(1, 1).Value = _
Application.Sum(.Resize(1, 4))
End If
Application.EnableEvents = True
End If
End With
End With
End Sub

Change the column in Cells(.Row, 1) to suit.


In article ,
"Chris" wrote:

Hi

I want to use VBA to do the following.

4 columns sum to equal the 5 column

entering a number into one of the 4 columns retotals the 5 column

changing the 5th column divides the new number by 4 and puts this value
into the each of the first 4 columns.

put another way:

Q1 + Q2 + Q3 +Q4 = Whole Year when columns 1 through 4 are edited

whole year/4 whole year/4 whole year/4 whole year/4 when column
whole
year is edited.

I suspect i'll have to do event capturing of cell clicks, move in, move
out,
up, down
and find where I am in the spreadsheet for the columns relative to the
whole
year column and vice versa

is this feasible or is there another way around this?

thanks

Chris