Cross Linking cells in 2 worksheets.

Good afternoon,

I am trying to get my hands on cross-linking cells in column Q with 2 other sheets, but in the same workbook, to change each other through a macro.

Like this code that only works on the same sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell1 As Range
Dim rCell2 As Range
If Target.Count > 1 Then Exit Sub
Set rCell1 = Range("A1")
Set rCell2 = Range("A2")
Set rCell3 = Range("A3")
Application.EnableEvents = False
Select Case Target.Address
Case rCell1.Address
rCell2.Value = rCell1.Value
rCell3.Value = rCell1.Value
Case rCell2.Address
rCell1.Value = rCell2.Value
rCell3.Value = rCell2.Value
Case rCell3.Address
rCell1.Value = rCell3.Value
rCell2.Value = rCell3.Value
End Select
Application.EnableEvents = True
End Sub

In this code you can place a value in any of the 3 cells, and the other 2 will change suit.
I need this to happen between 3 sheets.

I have 4 sheets, 1st sheet is the main sheet. Where I will put an Item delivered, pending, or dead in column Q.
The 2nd Sheet will have all pending deals, and the 3rd sheet will have all dead deals. They will most likely be on there by vlookup function.
Sheet 4 has all my drop down list, so its irrelevant right now.

What I need the spreadsheet to do, is put "pending" or "dead" in column Q for sheet1, I want that row to hide. And that hidden row to copy itself to it's corresponding sheet.
Sounds Easy right. Thats as far as I can go.

The hard part is when the "pending" or "dead" deal become "delivered"

I want to go to the pending sheet and change "Pending" on column Q, to "Delivered". Which is linked to the 1st sheet. The 1st sheet will unhide itself and will remove itself from the pending section.

And then do the same in the dead sheet. (unlikely, but just in case)

That is the hard part.

Any help in the right direction would be greatly appreciated.


Regards,
UnSpoknOne