Applying VBA logic to other cells

Hi all,

So I am stuck on how to go about finishing this sheet I created. The logic of this sheet works like this:

Ok, I have wrote a section of code that is fairly long and includes many different cells. My question is this: Can I use the VBA code that I have written and push that code to another cell (just the logic of how it works)? I do not just want to simply copy and paste the logic into a cell, but I want the logic to be changed to look at the cells and reformat all the cells to match the new location.

Here is part of my VBA code below. It basically consist of cell D21 with a dropdown selection of 1,2, or 3. It merges and changes cells based on the selection made in the dropdown. However, I want to move the "logic" part of it to D28. So now D21, if not touched, would not be merged with any other cells. Now I go to the next (D28) option and maybe I want that to be a 3 slot breaker. It would use the logic to merge all three together and so on. Doing this the "old fashion" way, I would have to go through the entire code and change each cell to it's appropriate new location. This is EXTREMELY time consuming and leads to a lot of error. There HAS to be an easier way to achieve this. I want the code to change based on the new location. So this is why a simple copy and paste will not work. Can this be done?




When a "1" is selected in cell D22 it will show the following:

1710168662782.png

When a "2" is selected in cell D22 it will show the following:

1710168803695.png

When a "3" is selected in cell D22 it will show the following:

1710168855472.png

This is only a part of my code however you can get an idea how I am doing it.

Private Sub sub4(ByVal Target As Range)
    If (Target.Address = "$D$22") And IsNumeric(Target.Value) Then
        Application.DisplayAlerts = False

        ' Unmerge cells before handling each case
        Range("C22:C40").UnMerge
        Range("D22:D40").UnMerge
        Range("C27:C28").Interior.ColorIndex = xlNone
        Range("C34:C35").Interior.ColorIndex = xlNone

        Select Case Target.Value
            Case 1
                HandleCase1
            Case 2
                HandleCase2
            Case 3
                HandleCase3
                
        End Select

        Application.DisplayAlerts = True
    End If
End Sub
   


Private Sub HandleCase1()
    
    ' Merges and unmerges cells based on Case1 being selected
    
    Range("C22:C26").Merge
    Range("C22").Value = "-"
    Range("C22:C26").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("C22:C26").Borders(xlEdgeBottom).Weight = xlMedium
    Range("C22:C26").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("C22:C26").Borders(xlEdgeLeft).Weight = xlMedium
    Range("C22:C26").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("C22:C26").Borders(xlEdgeRight).Weight = xlMedium
    Range("C27:C28").Interior.ColorIndex = xlNone

    
    Range("C29:C33").Merge
    Range("C29").Value = "-"
    Range("C29:C33").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("C29:C33").Borders(xlEdgeTop).Weight = xlMedium
    Range("C29:C33").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("C29:C33").Borders(xlEdgeBottom).Weight = xlMedium
    Range("C29:C33").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("C29:C33").Borders(xlEdgeLeft).Weight = xlMedium
    Range("C29:C33").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("C29:C33").Borders(xlEdgeRight).Weight = xlMedium
    
    Range("C36:C40").Merge
    Range("C36").Value = "-"
    Range("C36:C40").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("C36:C40").Borders(xlEdgeTop).Weight = xlMedium
    Range("C36:C40").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("C36:C40").Borders(xlEdgeLeft).Weight = xlMedium
    Range("C36:C40").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("C36:C40").Borders(xlEdgeRight).Weight = xlMedium
    Range("C34:C35").Interior.ColorIndex = xlNone

    
    Range("D22:D33").UnMerge
    Range("D22:D26").Merge
    Range("D27:D28").Interior.ColorIndex = xlNone
    Range("D34:D35").Borders(xlEdgeLeft).LineStyle = xlNone
    Range("D34:D35").Borders(xlEdgeRight).LineStyle = xlNone
    
    Range("C27:K28").Borders(xlEdgeLeft).LineStyle = xlNone
    Range("C27:K28").Borders(xlEdgeRight).LineStyle = xlNone
    Range("K27:K28").Borders(xlEdgeLeft).LineStyle = xlNone
    Range("D27:D28").Borders(xlEdgeLeft).LineStyle = xlNone
    Range("D27:D28").Borders(xlEdgeRight).LineStyle = xlNone
    
    Range("C34:K35").Borders(xlEdgeLeft).LineStyle = xlNone
    Range("C34:K35").Borders(xlEdgeRight).LineStyle = xlNone
    Range("K34:K35").Borders(xlEdgeLeft).LineStyle = xlNone
    
    Range("D22:D26").Borders(xlEdgeBottom).Weight = xlMedium
    Range("D22:D26").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("D22:D26").Borders(xlEdgeBottom).Weight = xlMedium
    Range("D22:D26").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("D22:D26").Borders(xlEdgeLeft).Weight = xlMedium
    Range("D22:D26").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("D22:D26").Borders(xlEdgeRight).Weight = xlMedium
    Range("D29:D33").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("D29:D33").Borders(xlEdgeTop).Weight = xlMedium
    Range("D29:D33").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("D29:D33").Borders(xlEdgeLeft).Weight = xlMedium
    Range("D29:D33").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("D29:D33").Borders(xlEdgeRight).Weight = xlMedium
    Range("D29:D33").Merge
    If Range("D29").Value = "" Then Range("D29").Value = 1
    Range("D34:D35").Interior.ColorIndex = xlNone
    
    Range("D36:D40").Merge
    Range("D29:D33").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("D29:D33").Borders(xlEdgeBottom).Weight = xlMedium
    Range("D29:D33").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("D29:D33").Borders(xlEdgeTop).Weight = xlMedium
    Range("D29:D33").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("D29:D33").Borders(xlEdgeLeft).Weight = xlMedium
    Range("D29:D33").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("D29:D33").Borders(xlEdgeRight).Weight = xlMedium
    Range("D36:D40").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("D36:D40").Borders(xlEdgeTop).Weight = xlMedium
    Range("D36:D40").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("D36:D40").Borders(xlEdgeLeft).Weight = xlMedium
    Range("D36:D40").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("D36:D40").Borders(xlEdgeRight).Weight = xlMedium
    If Range("D36").Value = "" Then Range("D36").Value = 1
    
    Range("E22:K33").UnMerge
    Range("E22:K26").Merge
    Range("E22:K26").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("E22:K26").Borders(xlEdgeBottom).Weight = xlMedium
    Range("E22:K26").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("E22:K26").Borders(xlEdgeLeft).Weight = xlMedium
    Range("E22:K26").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("E22:K26").Borders(xlEdgeRight).Weight = xlMedium
    
    Range("E29:K33").Merge
    Range("E29:K33").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("E29:K33").Borders(xlEdgeTop).Weight = xlMedium
    Range("E29:K33").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("E29:K33").Borders(xlEdgeLeft).Weight = xlMedium
    Range("E29:K33").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("E29:K33").Borders(xlEdgeRight).Weight = xlMedium
    Range("E29:K33").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("E29:K33").Borders(xlEdgeBottom).Weight = xlMedium
    If Range("E29").Value = "" Then Range("E29").Value = ""
    
    Range("E36:K40").Merge
    Range("E36:K40").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("E36:K40").Borders(xlEdgeTop).Weight = xlMedium
    Range("E36:K40").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("E36:K40").Borders(xlEdgeLeft).Weight = xlMedium
    Range("E36:K40").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("E36:K40").Borders(xlEdgeRight).Weight = xlMedium
    If Range("E36").Value = "" Then Range("E36").Value = ""
    
    Range("N22:N33").UnMerge
    Range("N22:N26").Merge
   
    Range("N22:N26").Merge
    Range("N22").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("N22").Borders(xlEdgeTop).Weight = xlThin
    Range("N26").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("N26").Borders(xlEdgeBottom).Weight = xlThin
    Range("N27:N28").Interior.ColorIndex = xlNone
            
    Range("N29:N33").Merge
    Range("N29").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("N29").Borders(xlEdgeTop).Weight = xlThin
    Range("N33").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("N33").Borders(xlEdgeBottom).Weight = xlThin
    Range("N34:N35").Interior.ColorIndex = xlNone
    
    Range("N36:N40").Merge
    Range("N36").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("N36").Borders(xlEdgeTop).Weight = xlThin
    Range("N40").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("N40").Borders(xlEdgeBottom).Weight = xlThin
    Range("N36:N40").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("N36:N40").Borders(xlEdgeLeft).Weight = xlThin
    Range("N36:N40").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("N36:N40").Borders(xlEdgeRight).Weight = xlThin
    Range("N41:N42").Interior.ColorIndex = xlNone
    
    Range("M22:M40").UnMerge
    Range("M22:M26").Merge
    Range("M29:M33").Merge
    Range("M36:M40").Merge
    
    
    Range("M22").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("M22").Borders(xlEdgeTop).Weight = xlThin
    Range("M26").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("M26").Borders(xlEdgeBottom).Weight = xlThin
       
    Range("M29").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("M29").Borders(xlEdgeTop).Weight = xlThin
    Range("M33").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("M33").Borders(xlEdgeBottom).Weight = xlThin
       
    Range("M36").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("M36").Borders(xlEdgeTop).Weight = xlThin
    Range("M40").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("M40").Borders(xlEdgeBottom).Weight = xlThin
    
    ' Insert bullet symbol in the range W23:X24 in Excel
    Range("W23:X24").Value = ChrW(&H2022)
    ' Remove bullet symbol from the ranges AA30 in Excel
    Range("AA30").Value = ""
    ' Remove bullet symbol from the ranges AA30 in Excel
    Range("AE37").Value = ""
    
    Range("AA30:AB31").UnMerge
    Range("AB37:AC38").UnMerge
    Range("AE37:AF38").UnMerge
    
    ' Add extra bold inside lines to the range AE37:AF38 in Excel
    Dim targetRange As Range
    Set targetRange = Union(Range("AA30:AB31"), Range("AE37:AF38"))
    
    ' Add extra bold inside vertical line
    With targetRange.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick ' Adjust the thickness as needed
    End With
    
    ' Add extra bold inside horizontal line
    With targetRange.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick ' Adjust the thickness as needed
    End With
    
     ' Set targetRange for the second range (T31:U34)
    Set targetRange2 = Range(("T31:U34"), ("T24:U27"))
    
    ' Remove the vertical line from the second range
    With targetRange2.Borders(xlInsideVertical)
        .LineStyle = xlNone
    End With
       
    ' Check if cell N22 is blank
If Range("N22").Value = "" Then
    ' If N22 is blank, set default value to "15 AMP"
    Range("N22").Value = "15 AMP"
End If
    ' Check if cell N29 is blank
If Range("N29").Value = "" Then
    ' If N29 is blank, set default value to "15 AMP"
    Range("N29").Value = "15 AMP"
End If
    ' Check if cell N36 is blank
If Range("N36").Value = "" Then
    ' If N36 is blank, set default value to "15 AMP"
    Range("N36").Value = "15 AMP"
End If

    ' Restore data validation for cells D29 & D36
    With Range("D29,D36").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="1,2,3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
 
    
    ' Restore data validation for cell N29 & N36
    With Range("N29,N36").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="15 AMP,20 AMP,25 AMP,30 AMP,40 AMP,45 AMP,50 AMP,60 AMP,70 AMP,80 AMP,90 AMP,100 AMP,110 AMP,125 AMP,150 AMP,175 AMP,200 AMP,225 AMP,250 AMP,300 AMP,350 AMP,400 AMP,N/A"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    
    ' Ensure Target is properly declared
Dim Target As Range
Set Target = Range("$D$22")



End Sub


Private Sub HandleCase2()

    ' Merges and unmerges cells based on Case2 being selected
    
    Range("C22:C33").Merge
    Range("C36:C40").Merge
    Range("C36").Value = "-"
    Range("C22:C33").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("C22:C33").Borders(xlEdgeBottom).Weight = xlMedium
    Range("C22:C33").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("C22:C33").Borders(xlEdgeLeft).Weight = xlMedium
    Range("C22:C33").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("C22:C33").Borders(xlEdgeRight).Weight = xlMedium
    Range("C34:C35").Borders(xlEdgeLeft).LineStyle = xlNone
    Range("C36:C40").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("C36:C40").Borders(xlEdgeTop).Weight = xlMedium
    Range("C36:C40").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("C36:C40").Borders(xlEdgeLeft).Weight = xlMedium
    Range("C36:C40").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("C36:C40").Borders(xlEdgeRight).Weight = xlMedium
    
    Range("D22:D33").Merge
    Range("D34").UnMerge
    Range("D36:D40").UnMerge
    
    Range("D22:D33").Merge
    Range("D22:D33").Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("D22:D33").Borders(xlEdgeBottom).Weight = xlMedium
    Range("D22:D33").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("D22:D33").Borders(xlEdgeLeft).Weight = xlMedium
    Range("D22:D33").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("D22:D33").Borders(xlEdgeRight).Weight = xlMedium
    Range("D34:D35").Interior.ColorIndex = xlNone
    
    Range("D36:D40").Merge
    Range("D36:D40").Borders(xlEdgeTop).LineStyle = xlContinuous
    Range("D36:D40").Borders(xlEdgeTop).Weight = xlMedium
    Range("D36:D40").Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("D36:D40").Borders(xlEdgeLeft).Weight = xlMedium
    Range("D36:D40").Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("D36:D40").Borders(xlEdgeRight).Weight = xlMedium
    If Range("D36").Value = "" Then Range("D36").Value = 1
    
    Range("E22:K33").Merge
    Range("E22:K40").UnMerge
 
End Sub