Microsoft Office Forums - View Single Post - [Solved] Intersect(Target, Sh.Range("AN4:AN5000")) can i reference this range another way?

trevorc · 03-02-2021, 08:36 PM

The problem I'm having is the range shown below "AN4:AN5000" is there any way to tell VBA this is a named range or selection. I can't add any columns to the sheet without changing a whole bunch of code. Only 1 example shown, I am using this in lots of columns.
P.S. the range is part of a large table.

Code:

  Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Sh.Range("AN4:AN5000")) Is Nothing Then
        ActiveCell.FormulaR1C1 = "Date :- " & Date & " , " & Intersect(acrow, tblColumns("Company Name").Range) & " INV Number :- " & Intersect(acrow, tblColumns("Invoice Number").Range) & ", Repairs to " & Intersect(acrow, tblColumns("Part").Range) & " " & " S/N " & Format(Intersect(acrow, tblColumns("Serial Number").Range), "000000000000") & " On " & Intersect(acrow, tblColumns("RMA Number").Range)
        With ActiveCell
            fst = InStr(8, ActiveCell, ":")
            'Format INV BOLD
            .Characters(fst + 3, ActiveCell.Characters.Count - 4).Font.Bold = True
            .Characters(fst + 7, ActiveCell.Characters.Count).Font.Bold = False
            'Format INV number size
            .Characters(fst + 3, ActiveCell.Characters.Count - 4).Font.size = 20
            .Characters(fst + 7, ActiveCell.Characters.Count).Font.size = 11
        End With
        Exit Sub
    End If