Greetings VB gods (esp Simon!).

This is complicated. I need code that starts when text A (eg) or text B is chosen from dropdown list in Cell L5 that will:
a. insert new row immediately below (in 26);
b. insert text A/B (whichever I chose) into L26;
c. replace what was originally in L25 (i.e. L25 will already say "DD-1/2". If I go back to L25 and reselect "STD" instead, it will take that as a command to insert new row immediately underneath, jam "STD" in L26, and not change what was in L25 above {DD-1/2}); and (now this is the tricky bit)
d. copy up/down/wherever the formulas in preceeding/following rows, and fix errors that appear in the formulas for the next row after that (N).

Problems encountered when trying to do this manually {right click - insert new row, copy row above - paste formula}:
A26, B26 copies fine,
C26 & D26 values have to be deleted (so C27 has to now reference D25, which it seems to be doing),
E26-P26 copies fine,
reference cell in formula in B28 needs to be changed from Q25 to Q26,
reference cell in formula in R27 needs to be changed from Q25 to Q26,
S26-W26 copies fine.

yes this is extremely complicated, but might (i hope) present a nice challenge for some smarty out there?!

I have code from something someone else wrote which does something similar, but can't even begin to know which bit it is:

Dim InsRng As String

'We deal with columns (E,F) 5 and 6 only
    
Select Case Target.Column
  '======= User changes column 5 =======
  Case 5
    'Get the range of the row
    StrtRng = Target.Offset(0, -4).Address(rowabsolute:=False, Columnabsolute:=False)
    EndRng = Target.Offset(0, 3).Address(rowabsolute:=False, Columnabsolute:=False)
    InsRng = StrtRng & ":" & EndRng
    
    Select Case Target.Value
    Case "INSERT:  Qw"
      InsertQwRow InsRng, Target
      If (Target.Offset(-1, 1).Value = "BLANK") Then
        'Set range to YELLOW
        Range(InsRng).Interior.Color = RGB(255, 255, 0)
        Target.Offset(0, -3).Value = Target.Offset(-1, -3).Value
      ElseIf (Target.Offset(-1, 1).Value = "") Then
        'Attention
        Target.Offset(-1, 1).Interior.Color = RGB(255, 0, 255)
      Else
        'Set the range to CYAN
        Range(InsRng).Interior.Color = RGB(0, 255, 255)
        Target.Offset(0, -3).Value = Target.Offset(-1, -3).Value
      End If
    Case "INSERT:  Sd"
      InsertSdRow InsRng, Target
      If (Target.Offset(0, 1).Value = "") Then
        'Set range to RED
        Range(InsRng).Interior.Color = RGB(255, 0, 0)
        Target.Offset(-1, -3).Value = Target.Offset(-2, -3).Value
        Target.Offset(-1, -2).Value = Target.Offset(-2, -2).Value
        Target.Offset(-1, -1).Value = Target.Offset(-2, -1).Value
        Formula = Target.Offset(-2, -4).Address(rowabsolute:=False)
        Target.Offset(-1, 1).FormulaArray = "=" & Formula        'set source sampleid
        Formula = Target.Offset(-2, 2).Address(rowabsolute:=False)
        Target.Offset(-1, 2).FormulaArray = "=" & Formula        'set sampletype
      Else
        'Attention
        Target.Offset(0, 1).Interior.Color = RGB(255, 0, 255)
      End If
    Case "** DELETE **"     'delete only Qw and Sd rows
      If (Target.Interior.Color = RGB(0, 255, 255) Or _
          Target.Interior.Color = RGB(255, 255, 0) Or _
          Target.Interior.Color = RGB(255, 0, 0) Or _
          Target.Interior.Color = RGB(255, 0, 255) Or _
          Target.Offset(0, 1).Interior.Color = RGB(255, 0, 255)) Then
        DeleteRow (InsRng)
      Else
        Target.Value = ""
      End If
    End Select
    
  '======= User changes column 6 =======
  Case 6
    'Get the range of the row
    StrtRng = Target.Offset(0, -5).Address(rowabsolute:=False, Columnabsolute:=False)
    EndRng = Target.Offset(0, 2).Address(rowabsolute:=False, Columnabsolute:=False)
    InsRng = StrtRng & ":" & EndRng
    Select Case Target.Value
      Case "BLANK"
        If (Target.Offset(0, -1).Value = "Qw") Then
          'Set range to YELLOW
          Range(InsRng).Interior.Color = RGB(255, 255, 0)
          Target.Offset(0, -4).Value = Target.Offset(-1, -4).Value
        Else
          'Attention
          Target.Offset(0, -1).Interior.Color = RGB(255, 0, 255)
        End If
      Case ""
        If (Target.Offset(0, -1).Value = "") Then
          'Set range to NORMAL
          Target.Offset(0, -5).Interior.Color = RGB(220, 220, 220)
          Target.Offset(0, -4).Interior.Color = RGB(255, 255, 255)
          Target.Offset(0, -3).Interior.Color = RGB(220, 255, 255)
          Target.Offset(0, -2).Interior.Color = RGB(220, 220, 220)
          Target.Offset(0, -1).Interior.Color = RGB(220, 220, 220)
          Target.Interior.Color = RGB(220, 220, 220)
          Target.Offset(0, 1).Interior.Color = RGB(220, 220, 220)
          Target.Offset(0, 2).Interior.Color = RGB(220, 220, 220)
        ElseIf (Target.Offset(0, -1).Value = "Sd") Then
          'Set range to RED
          Range(InsRng).Interior.Color = RGB(255, 0, 0)
          Target.Offset(0, -4).Value = Target.Offset(-1, -4).Value
          Target.Offset(0, -3).Value = Target.Offset(-1, -3).Value
          Target.Offset(0, -2).Value = Target.Offset(-1, -2).Value
          Target.Value = Target.Offset(-1, -5).Value
          Formula = Target.Offset(-1, -5).Address(rowabsolute:=False)
          Target.FormulaArray = "=" & Formula
        Else
          'Attention
          Target.Offset(0, -1).Interior.Color = RGB(255, 0, 255)
        End If
      Case "B1", "B2"
        If (Target.Offset(0, -1).Value = "Qw") Then
          'Set range to CYAN
          Range(InsRng).Interior.Color = RGB(0, 255, 255)
          Target.Offset(0, -4).Value = Target.Offset(-1, -4).Value
        Else
          'Attention
          Target.Offset(0, -1).Interior.Color = RGB(255, 0, 255)
        End If
    End Select
End Select

End Sub
The person who wrote this code is, obviously, an excel genius! I would try to adapt his workbook to what we need, but it's a lot more involved and quite surplus to requirements.

If you need to see my spreadsheet template I will gladly send it (and jump through some burning hoops ).

Regards,

gobbolino il novizio.