If one cell changes, then change the vale of other cells

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
Row 4, colC to ColJ

the have numbers in them ranging from .5 to 36 (they represent months).

The 15 rows benath row 4 contain dates in them.

How do i make it so that if a value in row 4 changes then the dates in the relevant column and in rows 5-19 adjust.

EG Row 4 ColD has 12 in it
ColD in row 5, 6, 7 and 8 respectively have the dates 12/12/05, 13/12/05, 24/12/05, 2/1/06.

If I change D4 to 24 then all the dates above would increase 12 months. ie 12/12/06, 13/12/06, 24/12/06, 2/1/07.

If i entered 3 in D4 then all dates above would decrease 8 months (or show 8 months earlier). ie 12/4/05, 13/4/05, 24/4/05, 2/5/05.

Thanks :)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

KJLoomis

New Member
Joined
Dec 10, 2005
Messages
28
It's a little messy, but if you paste this macro to the Micorsoft Excel Objects - Sheet for the sheet in question. This macro will automatically run and make adjustments to the dates in the 4 columns (Col 5,6,7,&8) next to column 4, when a new value is entered in column 4. Not my best work to date, but it should give you exactly what you are looking for. Test it extensively just to make sure.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Column = 4 And Target <> "" Then
If IsNumeric(Target.Value) = False Then
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
NewVal = Target.Value
Application.Undo
For Each Cell In Range(Cells(Target.Row, 5), Cells(Target.Row, 8)).Cells
If Cell.Value <> "" Then Cell.Value = DateAdd("m", NewVal - Target.Value, Cell.Value)
Next Cell
Target.Value = NewVal
End If

Application.EnableEvents = True

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,083
Messages
5,570,124
Members
412,305
Latest member
Mozz
Top