Automatically updating cells on different sheets when one cell is updated and vice versa.

Hi Everyone,

I hope someone can help- I have two sheets- a tracker and a gantt chart. When a date is changed in the tracker I need it to change in the gantt chart and vice versa. I have the following code that works fine for one project which has dates in column Q of the tracker and update E6-E106 in the gantt chart. I can't work out how to expand the code for more columns in the tracker, say column Y26:Y126 which I want to update E111:E211. Can anyone help?

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim r1 As Range, r2 As Range
    Set r1 = Sheets("GANTT CHART").Range("E6:E211")
    Set r2 = Sheets("Project Tracker").Range("Q26:Q126", "Y26:Y126")
    
    If Intersect(Target, r1) Is Nothing Then
   
    Application.EnableEvents = False
        r2.Value = r1.Value
 Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim r1 As Range, r2 As Range
    Set r1 = Sheets("Project Tracker").Range("Q26:Q126", "Y26:Y126")
    Set r2 = Sheets("GANTT CHART").Range("E6:E211")
   
    If Intersect(Target, r1) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r2.Value = r1.Value
Application.EnableEvents = True
End Sub

Moderator notes:
Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)