Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error GoTo EH 'On Error Resume Next
'Dim TRGVAL As Integer, TRGFORM As Integer
With Target.Interior
If Not .ColorIndex = xlNone Then
.ColorIndex = xlNone
ElseIf Not Intersect(Target, Range("F7:P402,V1:V5,AJ7:AJ402")) Is Nothing Then
If Range("AO1") = True Then
If Cells(Target.Row, 1) = 1 Then
If Cells(Target.Row, 39) = "" And Not Target.HasFormula Then
Cells(Target.Row, 39) = Target.Value
Cells(Target.Row, 40) = Target.Value
End If
If Target.HasFormula Then
Cells(Target.Row, 39) = Cells(Target.Row, 39) - Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J222").Value
If Cells(Target.Row, 39).Value < 0 Then
Cells(Target.Row, 39) = ""
Cells(Target.Row, 40) = ""
End If
Target.Value = Target.Formula & "+" & Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J222").Value
Else
Target.Value = "=AM" & Target.Row & "+" & Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J222").Value
Cells(Target.Row, 39) = Cells(Target.Row, 39) - Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J222").Value
'If Cells(Target.Row, 39) - Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J222").Value < 0 Then
'Cells(Target.Row, 39) = ""
'Cells(Target.Row, 40) = ""
'End If
End If
Else
Target.Value = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J222").Value
End If
If Not Target.Cells.CountLarge > 1 Then
If Not Intersect(Target, Range("F7:F402")) Is Nothing Then
If Cells(Target.Row, 1) = 1 Then
Target.Offset(, 22) = Application.Average(Target.Offset(, 22), Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value)
Else
Target.Offset(, 22) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value
End If
If Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221") > 0 Then
Target.Offset(, 1) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221").Value
End If
ElseIf Not Intersect(Target, Range("H7:H402")) Is Nothing Then
If Cells(Target.Row, 1) = 1 Then
Target.Offset(, 21) = Application.Average(Target.Offset(, 21), Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value)
Else
Target.Offset(, 21) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value
End If
If Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221") > 0 Then
Target.Offset(, 1) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221").Value
End If
ElseIf Not Intersect(Target, Range("J7:J402")) Is Nothing Then
If Cells(Target.Row, 1) = 1 Then
Target.Offset(, 20) = Application.Average(Target.Offset(, 20), Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value)
Else
Target.Offset(, 20) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value
End If
If Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221") > 0 Then
Target.Offset(, 1) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221").Value
End If
ElseIf Not Intersect(Target, Range("L7:L402")) Is Nothing Then
If Cells(Target.Row, 1) = 1 Then
Target.Offset(, 19) = Application.Average(Target.Offset(, 19), Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value)
Else
Target.Offset(, 19) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value
End If
If Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221") > 0 Then
Target.Offset(, 1) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221").Value
End If
ElseIf Not Intersect(Target, Range("N7:N402")) Is Nothing Then
If Cells(Target.Row, 1) = 1 Then
Target.Offset(, 18) = Application.Average(Target.Offset(, 18), Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value)
Else
Target.Offset(, 18) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J227").Value
End If
If Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221") > 0 Then
Target.Offset(, 1) = Workbooks(Range("AI1").Value).Worksheets("ITEMIZATION").Range("J221").Value
End If
End If
End If
Else
EH:
If Cells(Target.Row, 40) = "" Then Cells(Target.Row, 39).ClearContents
.ColorIndex = Range("T2").Value
If Cells(Target.Row, 1) = 1 Then
If Left(Target.Formula, 3) = "=AM" Then
Dim Refkill As String
Refkill = Left(Target.Formula, InStr(1, Target.Formula, "+"))
Target = "=" & Replace(Target.Formula, Refkill, "")
End If
End If
End If
ElseIf Not Intersect(Target, Range("AJ7:AJ402")) Is Nothing Then
.ColorIndex = 20
End If
End With
'EH:
Cancel = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Error
Application.EnableEvents = False
If Range("AN1") <> False Then
If Not Target.Cells.CountLarge > 1 Then
If Not Intersect(Target, Range("F7:F402")) Is Nothing Then
Target.Offset(, 22).Select
ElseIf Not Intersect(Target, Range("H7:H402")) Is Nothing Then
Target.Offset(, 21).Select
ElseIf Not Intersect(Target, Range("J7:J402")) Is Nothing Then
Target.Offset(, 20).Select
ElseIf Not Intersect(Target, Range("L7:L402")) Is Nothing Then
Target.Offset(, 19).Select
ElseIf Not Intersect(Target, Range("N7:N402")) Is Nothing Then
Target.Offset(, 18).Select
End If
End If
If Not Target.Cells.CountLarge > 1 Then
If Not Intersect(Target, Range("AB7:AB402")) Is Nothing Then
If Not Target.Offset(, -22).HasFormula Then
Target.Offset(, -22).Interior.ColorIndex = Range("T2").Value
End If
ElseIf Not Intersect(Target, Range("AC7:AC402")) Is Nothing Then
If Not Target.Offset(, -21).HasFormula Then
Target.Offset(, -21).Interior.ColorIndex = Range("T2").Value
End If
ElseIf Not Intersect(Target, Range("AD7:AD402")) Is Nothing Then
If Not Target.Offset(, -20).HasFormula Then
Target.Offset(, -20).Interior.ColorIndex = Range("T2").Value
End If
ElseIf Not Intersect(Target, Range("AE7:AE402")) Is Nothing Then
If Not Target.Offset(, -19).HasFormula Then
Target.Offset(, -19).Interior.ColorIndex = Range("T2").Value
End If
ElseIf Not Intersect(Target, Range("AF7:AF402")) Is Nothing Then
If Not Target.Offset(, -18).HasFormula Then
Target.Offset(, -18).Interior.ColorIndex = Range("T2").Value
End If
End If
End If
'If Not Range("AJ7:AJ402").ColorIndex = xlNone Then
'.ColorIndex = xlNone
If Not Intersect(Target, Range("AJ7:AJ402")) Is Nothing Then
If Target = "" Then
Target.Interior.ColorIndex = xlNone
ElseIf Target <> "" Then
Target.Interior.ColorIndex = 20
End If
End If
End If
KeepGoing:
Application.EnableEvents = True
Exit Sub
Error:
MsgBox Err.Description
Resume KeepGoing
End Sub