I created a multi-worksheet workbook that has multiple instances of bi-directional (and in some cases tri-) cell updating. For instance, If I change B1 on worksheet 1, B2 on worksheet 2 changes, and if I change B2 on worksheet 2, then B1 on worksheet 1 changes. The issue is I have created this massive spreadsheet and have no flexibility. I need to add/delete rows without affecting the integrity of the code. It was suggested I use named ranges. So, great, sounds good. I then changed the relevant cell references to named ranges ("cost", "revenue", etc.) and inserted in place of B1, B2, etc. Doesn't work. The following is some of the code from one of the sheets (before I changed to named ranges, and it still worked).
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case "I1" 'Payment Processing Fee
Sheets("Overview").Range("T4") = Target
Case "I2" 'Cost bandwidth
Sheets("Overview").Range("T5") = Target
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
When I change the T4 and T5 to a named range, but leave the Case "I1" and "I2" alone, it works. If I change the Case names, it doesn't work. Has me stumped and very frustrated! Thanks in advance!
Bookmarks