Sorry for not using the second code when you said that I'm overloading the event,
I noticed that YearCount was = Target.Column - 9. If you want to use the Select Case, just move it to the SetYearCount function.
A large part of the problem is that you are using Merged Cells. That forces you to use code structures like
Sub I3O3(Target As Range)
With Sheets("Main")
.Range("I10") = Target.Value
.Range("O5") = Sheets("Original Data").Range("D3").Value
.Range("G5") = Sheets("Original Data").Range("A2").Value
.Range("I5") = Sheets("Original Data").Range("C3").Value
.Range("K5") = Sheets("Original Data").Range("B3").Value
.Range("O14") = GetO14
.Range("Q10") = GetQ10
End With
Sheets("Main").Activate
End Sub
Sub I4O4(Target As Range)
With Sheets("Main")
.Range("I10") = Target.Value
.Range("O5") = Sheets("Original Data").Range("D4").Value
.Range("Q10") = GetQ10
End With
Sheets("Main").Activate
End Sub
Using that structure means that you MUST remember to select the appropriate row to fill in parts of Main before you then select the Row to fill in the rest of Main.
Range("A2:A13") is merged. IF you unmerge the range and put "Building 1" in all the cells, and did the same for all the Buildings...
This Code would replace all of your current code
'Standard Module Code
Public YearCount As Long
'Main Code
Private Sub Worksheet_Change(ByVal Target As Range)
Range("Q10") = (Range("G10") - Range("K10") - Range("O14")) / YearCount
End Sub
'Original Data Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PayPlan As Worksheet
Dim OData As Worksheet
Dim Rw As Long
Set PayPlan = Sheets("Main")
Set OData = Sheets("Original Data")
Rw = Target.Row
YearCount = Target.Column - 9
With PayPlan
.Range("G5") = OData.Cells(Rw, "A")
.Range("I5") = OData.Cells(Rw, "C")
.Range("K5") = OData.Cells(Rw, "B")
.Range("O5") = OData.Cells(Rw, "D")
.Range("I10") = Target.Value
'.Range("O10") = ???
.Range("O14") = .Range("O10") * YearCount * 12
.Range("Q10") = (.Range("G10") - .Range("K10") - .Range("O14")) / YearCount
End With
Sheets("Main").Activate
End Sub