I need help combining 2 sections of code. I want them to run together. The first section of code runs one side of my sheet and the second section runs the other side of the sheet. I am having a terrible time combining it so that they place nice together.
Here is the first section of code:
Here is the second section of code:
Here is the first section of code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const dd_max As Integer = 36 ' <<< number of datavalidations
With ActiveSheet
With Target
If .Count > 1 Then Exit Sub
If .Column <> 4 Then Exit Sub
ay = .Row
dd = (ay - 15) / 7
If dd < 1 Then Exit Sub
If dd > dd_max Then
With Application
.EnableEvents = False
Beep
.Undo
.EnableEvents = True
Exit Sub
End With
End If
ay0 = ay + 11
ay1 = ay + 14
ay2 = ay + 13
ay3 = ay + 18
End With
With Application
.DisplayAlerts = False
.EnableEvents = False
End With
If (ay - 22) Mod 7 = 0 Then
bg = .Range("D22").Interior.Color
With .Range(.Cells(ay, "D"), .Cells(ay3, "K"))
.UnMerge
.Borders.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
With .Range(.Cells(ay, "N"), .Cells(ay3, "N"))
.UnMerge
.Borders.LineStyle = xlNone
.Interior.ColorIndex = xlNone
End With
For y = 0 To 2
dv = "1,2,3"
If dd + y > dd_max - 2 Then dv = "1,2"
If dd + y > dd_max - 1 Or Target.Value <> 1 Then dv = "1"
If dd + y <= dd_max Then
With .Cells(ay + y * 7, "D").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=dv
.IgnoreBlank = True: .InCellDropdown = True
.ShowInput = True: .ShowError = True
End With
End If
Next y
Select Case Target.Value
Case 1
For y = 2 To 0 Step -1
If dd + y <= dd_max Then
ayy7 = ay + y * 7
With .Range(.Cells(ayy7, "E"), .Cells(ayy7 + 4, "K"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlThin
End With
With .Range(.Cells(ayy7, "D"), .Cells(ayy7 + 4, "D"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
With .Range(.Cells(ayy7, "N"), .Cells(ayy7 + 4, "N"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
.Cells(ayy7, "D") = 1
.Cells(ayy7, "E") = "Heat Pump"
.Cells(ayy7, "N") = "15AMP"
End If
Next y
Case 2
With .Range(.Cells(ay, "E"), .Cells(ay0, "K"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlThin
End With
With .Range(.Cells(ay, "D"), .Cells(ay0, "D"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
With .Range(.Cells(ay, "N"), .Cells(ay0, "N"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
.Cells(ay, "D") = 2
.Cells(ay, "N") = "15AMP"
If dd + 1 < dd_max Then
With .Range(.Cells(ay1, "E"), .Cells(ay3, "K"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlThin
End With
With .Range(.Cells(ay1, "D"), .Cells(ay3, "D"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
With .Range(.Cells(ay1, "N"), .Cells(ay3, "N"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
.Cells(ay1, "D") = 1
.Cells(ay1, "N") = "15AMP"
End If
Case 3
With .Range(.Cells(ay, "E"), .Cells(ay3, "K"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlThin
End With
With .Range(.Cells(ay, "D"), .Cells(ay3, "D"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
With .Range(.Cells(ay, "N"), .Cells(ay3, "N"))
.Merge
.BorderAround , ColorIndex:=5, Weight:=xlMedium
.Interior.Color = bg
End With
End Select
.Cells(ay - 1, "D").Activate
Else
With Application
Beep
.EnableEvents = False
.Undo
End With
End If
End With
With Application
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
With Target
If .Column <> 4 Then Exit Sub
ay = .Row
yx = .Value
dd = (ay - 15) / 7
If dd < 1 Then Exit Sub
If dd > dd_max Then
Beep
Exit Sub
End If
End With
If (ay - 22) Mod 7 = 0 Then
If yx(1, 1) = Empty Then
With .Cells(ay, "D").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1"
.IgnoreBlank = True: .InCellDropdown = True
.ShowInput = True: .ShowError = True
End With
Else
If yx(1, 1) <> 1 Then .Cells(ay, "D") = 1
End If
End If
End With
End SubHere is the second section of code:
VBA Code:
Const dd_max As Integer = 36 ' <<< number of datavalidations
With ActiveSheet
With Target
If .Count > 1 Then Exit Sub
If .Column <> 52 Then Exit Sub
ay = .Row
dd = (ay - 15) / 7
If dd < 1 Then Exit Sub
If dd > dd_max Then
With Application
.EnableEvents = False
Beep
.Undo
.EnableEvents = True
Exit Sub
End With
End If
ay0 = ay + 11
ay1 = ay + 14
ay2 = ay + 13
ay3 = ay + 18
End With
With Application
.DisplayAlerts = False
.EnableEvents = False
End With
If (ay - 22) Mod 7 = 0 Then
With .Range(.Cells(ay, "AZ"), .Cells(ay3, "AS"))
.UnMerge
.Borders.LineStyle = xlNone
End With
With .Range(.Cells(ay, "AP"), .Cells(ay3, "AP"))
.UnMerge
.Borders.LineStyle = xlNone
End With
For y = 0 To 2
dv = "1,2,3"
If dd + y > dd_max - 2 Then dv = "1,2"
If dd + y > dd_max - 1 Or Target.Value <> 1 Then dv = "1"
If dd + y <= dd_max Then
With .Cells(ay + y * 7, "AZ").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=dv
.IgnoreBlank = True: .InCellDropdown = True
.ShowInput = True: .ShowError = True
End With
End If
Next y
Select Case Target.Value
Case 1
For y = 0 To 2
If dd + y <= dd_max Then
ayy7 = ay + y * 7
With .Range(.Cells(ayy7, "AZ"), .Cells(ayy7 + 4, "AZ"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ayy7, "AY"), .Cells(ayy7 + 4, "AS"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ayy7, "AP"), .Cells(ayy7 + 4, "AP"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlThin
End With
.Cells(ayy7, "AZ") = 1
.Cells(ayy7, "AY") = "Heat Pump"
.Cells(ayy7, "AP") = "15AMP"
End If
Next y
Case 2
With .Range(.Cells(ay, "AZ"), .Cells(ay0, "AZ"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ay, "AY"), .Cells(ay0, "AS"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ay, "AP"), .Cells(ay0, "AP"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlThin
End With
.Cells(ay, "AZ") = 2
.Cells(ay, "AP") = "15AMP"
If dd + 1 < dd_max Then
With .Range(.Cells(ay1, "AZ"), .Cells(ay3, "AZ"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ay1, "AY"), .Cells(ay3, "AS"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ay1, "AP"), .Cells(ay3, "AP"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlThin
End With
.Cells(ay1, "AZ") = 1
.Cells(ay1, "AP") = "15AMP"
End If
Case 3
With .Range(.Cells(ay, "AZ"), .Cells(ay3, "AZ"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ay, "AY"), .Cells(ay3, "AS"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlMedium
End With
With .Range(.Cells(ay, "AP"), .Cells(ay3, "AP"))
.Merge
.BorderAround , ColorIndex:=1, Weight:=xlThin
End With
End Select
Else
With Application
Beep
.EnableEvents = False
.Undo
End With
End If
End With
With Application
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
With Target
If .Count > 1 Then Exit Sub
If .Column <> 52 Then Exit Sub
ay = .Row
yx = .Value
dd = (ay - 15) / 7
If dd < 1 Then Exit Sub
If dd > dd_max Then
Beep
Exit Sub
End If
End With
If (ay - 22) Mod 7 = 0 Then
If yx = Empty Then
With .Cells(ay, "AZ").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1"
.IgnoreBlank = True: .InCellDropdown = True
.ShowInput = True: .ShowError = True
End With
End If
End If
End With
End Sub
