VBA to dynamically assign date in one cell based on manual input to several other cells in same row?

[IHRAcer]

I have once again reached the limit of my VBA knowledge and am here seeking help. Here's what I'd like to accomplish:

I have a worksheet list of projects, one project per row. I want Column B to be the 'Next Step By' date for each row. Users will be manually entering dates into Columns C, D, and E for each step of each project. There are different time intervals between each step, and I would like Column B to update based on input into each of these three columns in succession. When the first step is started, the next step of the project should be started within 10 days, then the next step within 30 days after that, then 60 days after that. For example:

The user starts a project and enters a date in a row in Column C, and the cell in Column B in that row displays the date 10 days in the future from that.
When the user then enters the next date in Column D, the cell in Column B then updates to display a new date that is 30 days in the future from the one entered in Column D.
When the user enters another date in Column E, then the cell in Column B updates yet again to display a date that is 60 days in the future from the one entered in Column E.

The idea is to create a sheet that is sortable by the 'Next Step By' date in order to easily keep track of which project needs attention first.

Is what I'm asking possible?

 

[Norie]

Here's some simple code that will do what you want.

You might want to add a few things to it, e.g. check that it's actually a date that's been entered, check if other columns in the row are empty or not etc, but it should give you a start.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("C:E")) Is Nothing Then
        
        Application.EnableEvents = False
        
        Range("B" & Target.Row).Value = Target.Value + Choose(Target.Column - 2, 10, 30, 60)
                
        Application.EnableEvents = True
    End If
    
End Sub

 

[IHRAcer]

Here's some simple code that will do what you want.

You might want to add a few things to it, e.g. check that it's actually a date that's been entered, check if other columns in the row are empty or not etc, but it should give you a start.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Not Intersect(Target, Range("C:E")) Is Nothing Then
       
        Application.EnableEvents = False
       
        Range("B" & Target.Row).Value = Target.Value + Choose(Target.Column - 2, 10, 30, 60)
               
        Application.EnableEvents = True
    End If
   
End Sub

That is EXACTLY what I wanted, thank you so much! I really appreciate the help that I always get from this site.

 

[IHRAcer]

Sorry to revive an older thread, but I'm hoping that someone has a workaround. This code works great for my purposes, but will return an error and stop all macros on the sheet if the user:

- Enters a number that isn't in date format
- Deletes a line from the sheet

Debugging on either occurrence brings me to this macro. Can anyone help me understand what I need to do to fix it?

 

[Norie]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count > 1 Then Exit Sub    

    If Not Intersect(Target, Range("C:E")) Is Nothing Then
        
        If IsDate(Target.Value) Then
            Application.EnableEvents = False
        
            Range("B" & Target.Row).Value = Target.Value + Choose(Target.Column - 2, 10, 30, 60)
                
            Application.EnableEvents = True
        End If
    End If
    
End Sub

 

[IHRAcer]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Target.Cells.Count > 1 Then Exit Sub   

    If Not Intersect(Target, Range("C:E")) Is Nothing Then
       
        If IsDate(Target.Value) Then
            Application.EnableEvents = False
       
            Range("B" & Target.Row).Value = Target.Value + Choose(Target.Column - 2, 10, 30, 60)
               
            Application.EnableEvents = True
        End If
    End If
   
End Sub

That seems to have taken care of both problems! Thank you again so much!