Hey, so I am working on some code that watches a range for cell value changes, then runs a macro based off a data validation list. At first I had it watch just single cell changes, and it worked that way just fine. But in testing the guys who use it found it would be nice if they could copy and paste the list from one workbook to another. This is for invoicing based off field work.

so here is my code presently

The problem I suppose is in the watched area it is merged with rows B and C. So it is looping through each cell as if they aren't just a single cell.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SPN, rng As Range
    Dim wb As String
    
    Set SPN = Target.Parent.Range("A16:A39")             'Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
    
        'If Target.Count > 1 Then Exit Sub                   'Only look at single cell changes
        If Intersect(Target, SPN) Is Nothing Then Exit Sub  'Only look at that range
    
        'Action if Condition(s) are met (do your thing here...)
        On Error GoTo error
        For Each rng In Target
        rng.Offset(0, 1).Value = "=iferror(vlookup(" & rng.MergeArea.Cells(1).Address & ",tblspn,2,false),"""")&"" """
        rng.Offset(0, 8).Value = "=iferror(vlookup(" & rng.mergaarea.Cells(1).Address & ",tblspn,3,false),"""")&"" """
        Select Case rng.Value
            Case Is = "SLB-CAN-DAY"
                rng.Offset(0, 6).Value = "=sum(q22,q28)"
            Case Is = "SLB-CAN-STDBY"
                rng.Offset(0, 6).Value = "=sum(q23,q29)"
            Case Is = "SLB-CAN-KM"
                rng.Offset(0, 6).Value = "=sum(q24,q30)"
            Case Is = "SLB-CAN-HOTEL"
                rng.Offset(0, 6).Value = "=sum(q25,q31)"
            Case Is = "SLB-CAN-SUBSIS"
                rng.Offset(0, 6).Value = "=sum(q26,q32)"
            Case Is = "" 'this case is here to clean the sheet if a spn code is removed from the line
                rng.Offset(0, 1).Value = ""
                rng.Offset(0, 6).Value = ""
                rng.Offset(0, 8).Value = ""
            Case Else   'this case removes the 'sum' formulas from spn codes that don't require it. but this would essentially loop over top of the spn codes that do require it since it is looping through the merged area.
                rng.Offset(0, 6).Value = ""
        End Select
        Next rng
        
Exit Sub
        
error:
    Target.Offset(0, 1).Value = ""
    Target.Offset(0, 6).Value = ""
    Target.Offset(0, 8).Value = ""
End Sub
I find what is happening that it will first put my correct formula's in, then it gets to the select case part and eventually sees that cells in row "B" have a empty value then sweep through and delete the formulas that are supposed to be in.

on searching my issue I see a lot of people that use vba say stay away from merged cells, lol. But the cells are merged so other cells can look nice, and so the users of the book don't get confused. Also, I'm still quite novice at vba.