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

#### The Idea Dude

##### Well-known Member
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

### 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
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

Replies
10
Views
227
Replies
17
Views
569
Replies
2
Views
84
Replies
2
Views
71
Replies
9
Views
109

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