Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long, j As Long, Lr1 As Long, Lr2 As Long, Cr1 As Long, Cr1R As String, Cr2 As Long, Cr2R As String
Dim Lr3 As Long, Lr4 As Long, M1 As Long, M2 As Long, Cr3 As Variant
Lr1 = Range("I" & Rows.Count).End(xlUp).Row
Lr2 = Range("N" & Rows.Count).End(xlUp).Row
Lr3 = Sheets("Work").Range("A" & Rows.Count).End(xlUp).Row
Lr4 = Sheets("Paper").Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
If Intersect(Target, Union(Range("I" & Lr1), Range("N" & Lr2))) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("I" & Lr1)) Is Nothing Then
Application.EnableEvents = False
Cr3 = Application.InputBox(prompt:="Please Input New Customer Name", Type:=2)
If Cr3 = False Then
Application.EnableEvents = True
Exit Sub
Else
Range("I" & Lr1 & ":M" & Lr1).Insert Shift:=xlDown
i = Application.WorksheetFunction.Match(Cr3, Sheets("Work").Range("A1:A" & Lr3), 0)
If i = False Then
i = Application.WorksheetFunction.Match("New Customer", Sheets("Work").Range("A1:A" & Lr3), 0)
Sheets("Work").Range("A" & i & ":G" & i + 3).Copy Sheets("Work").Range("A" & i + 4 & ":G" & i + 7)
Sheets("Work").Range("A" & i).Value = Cr3
End If
End If
Range("I" & Lr1).Value = Sheets("Work").Range("A" & i).Value
If Lr1 = 3 Then
Range("J" & Lr1).FormulaR1C1 = "=IFERROR(INDEX(Work!C[-8],MATCH(R[1]C9,Work!C[-9],0)-2,0),"""")"
Range("K" & Lr1).FormulaR1C1 = "=IFERROR(SUM(INDEX(Work!C[-7],MATCH(RC9,Work!C[-10],0)+1,0):INDEX(Work!C[-6],MATCH(R[1]C9,Work!C[-10],0)-2,0)),"""")"
Range("L" & Lr1).FormulaR1C1 = "=IFERROR(SUM(INDEX(Work!C[-6],MATCH(RC9,Work!C[-11],0)+1,0):INDEX(Work!C[-5],MATCH(R[1]C9,Work!C[-11],0)-2,0)),"""")"
ElseIf Lr1 > 3 Then
Range("J" & Lr1 - 1 & ":J" & Lr1).FormulaR1C1 = "=IFERROR(INDEX(Work!C[-8],MATCH(R[1]C9,Work!C[-9],0)-2,0),"""")"
Range("K" & Lr1 - 1 & ":K" & Lr1).FormulaR1C1 = "=IFERROR(SUM(INDEX(Work!C[-7],MATCH(RC9,Work!C[-10],0)+1,0):INDEX(Work!C[-6],MATCH(R[1]C9,Work!C[-10],0)-2,0)),"""")"
Range("L" & Lr1 - 1 & ":L" & Lr1).FormulaR1C1 = "=IFERROR(SUM(INDEX(Work!C[-6],MATCH(RC9,Work!C[-11],0)+1,0):INDEX(Work!C[-5],MATCH(R[1]C9,Work!C[-11],0)-2,0)),"""")"
End If
Cr1R = Range("A" & i).Address
Sheets("Dashboard").Hyperlinks.Add Anchor:=Range("I" & Lr1), Address:="", SubAddress:="'" & Sheets("Work").Name & "'!" & Cr1R, TextToDisplay:=Range("I" & Lr1).Value
With Sheets("Dashboard").Range("I" & Lr1).Font
.Underline = xlUnderlineStyleNone
.ColorIndex = xlColorIndexAutomatic
.Name = "Arial"
.Size = 14
End With
Sheets("Work").Activate
Sheets("Work").Range("A" & i).Select
Sheets("Work").Range("A" & i).Font.ColorIndex = 1
Application.EnableEvents = True
End If
If Not Intersect(Target, Range("N" & Lr2)) Is Nothing Then
If Target.Count = 1 Then
Application.EnableEvents = False
Cr3 = Application.InputBox(prompt:="Please Input New Customer Name", Type:=2)
If Cr3 = False Then
Application.EnableEvents = True
Exit Sub
Else
Range("N" & Lr2 & ":Q" & Lr2).Insert Shift:=xlDown
i = Application.WorksheetFunction.Match(Cr3, Sheets("Paper").Range("A1:A" & Lr4), 0)
If i = False Then
i = Application.WorksheetFunction.Match("New Customer", Sheets("Paper").Range("A1:A" & Lr4), 0)
Sheets("Paper").Range("A" & i & ":G" & i + 3).Copy Sheets("Paper").Range("A" & i + 4 & ":G" & i + 7)
Sheets("Paper").Range("A" & i).Value = Cr3
End If
End If
Range("N" & Lr2).Value = Sheets("Paper").Range("A" & i).Value
If Lr2 = 3 Then
Range("O" & Lr2).FormulaR1C1 = "=IFERROR(INDEX(Paper!C[-13],MATCH(R[1]C14,Paper!C[-14],0)-2,0),"""")"
Range("P" & Lr2).FormulaR1C1 = "=IFERROR(SUM(INDEX(Paper!C[-12],MATCH(RC14,Paper!C[-15],0)+1,0):INDEX(Paper!C[-11],MATCH(R[1]C14,Paper!C[-15],0)-2,0)),"""")"
Range("Q" & Lr2).FormulaR1C1 = "=IFERROR(SUM(INDEX(Paper!C[-11],MATCH(RC14,Paper!C[-16],0)+1,0):INDEX(Paper!C[-10],MATCH(R[1]C14,Paper!C[-16],0)-2,0)),"""")"
ElseIf Lr2 > 3 Then
Range("O" & Lr2 - 1 & ":O" & Lr2).FormulaR1C1 = "=IFERROR(INDEX(Paper!C[-13],MATCH(R[1]C14,Paper!C[-14],0)-2,0),"""")"
Range("P" & Lr2 - 1 & ":P" & Lr2).FormulaR1C1 = "=IFERROR(SUM(INDEX(Paper!C[-12],MATCH(RC14,Paper!C[-15],0)+1,0):INDEX(Paper!C[-11],MATCH(R[1]C14,Paper!C[-15],0)-2,0)),"""")"
Range("Q" & Lr2 - 1 & ":Q" & Lr2).FormulaR1C1 = "=IFERROR(SUM(INDEX(Paper!C[-11],MATCH(RC14,Paper!C[-16],0)+1,0):INDEX(Paper!C[-10],MATCH(R[1]C14,Paper!C[-16],0)-2,0)),"""")"
End If
Cr2R = Range("A" & i).Address
Sheets("Dashboard").Hyperlinks.Add Anchor:=Range("N" & Lr2), Address:="", SubAddress:="'" & Sheets("Paper").Name & "'!" & Cr2R, TextToDisplay:=Range("N" & Lr2).Value
With Sheets("Dashboard").Range("N" & Lr2).Font
.Underline = xlUnderlineStyleNone
.ColorIndex = xlColorIndexAutomatic
.Name = "Arial"
.Size = 14
End With
Sheets("Paper").Activate
Sheets("Paper").Range("A" & i).Select
Sheets("Paper").Range("A" & i).Font.ColorIndex = 1
Application.EnableEvents = True
End If
End If
End Sub