Excel VBA sheet synchronization of common data

[Vulcanzier]

[FONT=&quot]Hi all,[/FONT]
[FONT=&quot]I am building a taskboard on excel. I have built it so that the user can click/ tap to cycle through colors red, green, yellow in order to signify completeness of the task. this is on the master Sheet. I'm doing this by running the following macro: [/FONT]
[FONT=&quot] [/FONT][FONT=&quot]Private Sub Worksheet_SelectionChange(ByVal Target As Range)


'Then change the background to the specified color
'Target.Value = "Not Complete"

If Target.Value = "Not Complete" Then

'But if the target cell is already the specified color
Target.Value = "In Progress"

'Then change the background to the specified color
ElseIf Target.Value = "In Progress" Then

'But if the target cell is already the specified color
Target.Value = "Complete"

'Then change the background to the specified color
ElseIf Target.Value = "Complete" Then

Target.Value = "Not Complete"

End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Cancel = True
Worksheet_SelectionChange Target

End Sub
[/FONT]
[FONT=&quot]I have created separate sheets that correspond directly withe the Master sheet, with the same tasks, just blow up so they can be read better. My ultimate goal is to be able to cycle through the colors on the Master sheet and have it link to the ones on the separate task sheets, and visa versa. being able to cycle on the blow up sheets and have it feed to the master. I know how to make it go one way ie, master to separate sheets, (using a copy paste special link) but i don't know how to make the separate sheets feed back to the master. However the selections do not line up perfectly, ie: what is in A7 on the master could be on B5 on the other sheet. [/FONT]
[FONT=&quot]Thank you in advanced for your insight![/FONT]
[FONT=&quot]So i was able to mirror the sheets exactly, using this code: [/FONT]
<code style="border: 0px; font-weight: inherit; font-style: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; margin: 0px; outline: 0px; padding: 0px; white-space: inherit;">Private Sub Worksheet_Change(ByVal Target As Range)

If ThisWorkbook.Sheets("Sheet1").Range(Target.Address).Value <> Target.Value Then

ThisWorkbook.Sheets("Sheet1").Range(Target.Address).Value = Target.Value

End If

End Sub</code>[FONT=&quot]The sheets can go back and forth. the only problem is the Separate sheets are not mirroring the Master sheet in layout. example the data in cells A3:A23 on the master our split into 4 different ranges on the separate sheet A2:11, D3:D8, G2:G3, J2:J3[/FONT]

 

[AlphaFrog]

There are more cells in A3:A23 than in A2:A11, D3:D8, G2:G3, J2:J3. So I'm not sure how they align.

Put something like this in sheet1

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    Application.EnableEvents = [color=darkblue]False[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] Reenable
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("A2:A22"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        Sheets("Sheet2").Range("A2:A11").Value = Range("A3:A11").Value
        Sheets("Sheet2").Range("D3:D9").Value = Range("A13:A19").Value
        Sheets("Sheet2").Range("G2:G3").Value = Range("A20:A21").Value
        Sheets("Sheet2").Range("J2:J3").Value = Range("A22:A23").Value
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
Reenable:
    Application.EnableEvents = [color=darkblue]True[/color]
    [color=darkblue]If[/color] Err.Number <> 0 [color=darkblue]Then[/color] MsgBox Err.Description, vbCritical, "Error " & Err.Number
    
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Put something like this in Sheet2

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    Application.EnableEvents = [color=darkblue]False[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] Reenable
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("A2:A11,D3:D9,G2:G3,J2:J3"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        Sheets("Sheet1").Range("A3:A12").Value = Range("A2:A11").Value
        Sheets("Sheet1").Range("A13:A19").Value = Range("D3:D9").Value
        Sheets("Sheet1").Range("A20:A21").Value = Range("G3:G3").Value
        Sheets("Sheet1").Range("A22:A23").Value = Range("J2:J3").Value
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
Reenable:
    Application.EnableEvents = [color=darkblue]True[/color]
    [color=darkblue]If[/color] Err.Number <> 0 [color=darkblue]Then[/color] MsgBox Err.Description, vbCritical, "Error " & Err.Number
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

The Events are suspended so that each procedure doesn't trigger the other creating an infinite loop.

Also, see my signature block below about the use of CODE tags. It makes reading your post much easier.