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

IHRAcer

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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

New Member
Joined
Apr 6, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,358
Messages
5,571,706
Members
412,413
Latest member
dvprajapati
Top