excel - Dynamical row links for VBA

Question

Does anyone know how to make the row numbers dynamic in my VBA code? For example, Row62 would always reference that particular line item even if a row was inserted above it and is now technically Row63.

The code is currently as follows:

Sub HistoricalMacro()
    Dim c As Long    
    c = Selection.Column

    Application.ScreenUpdating = False     

    'Format cells
    Cells(62, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"    
    Cells(72, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"    
    Cells(78, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"    
    Cells(79, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"   
     
    'Link cells Expense Summary sheet    
    Cells(55, c).FormulaR1C1 = "='Expense Summary'!R[-1]C"    
    Cells(59, c).Resize(5).FormulaR1C1 = "='Expense Summary'!R[-3]C"    
    Cells(68, c).Resize(5).FormulaR1C1 = "='Expense Summary'!R[-6]C"    
    Cells(76, c).Resize(4).FormulaR1C1 = "='Expense Summary'!R[-9]C"    
     
    'Update expense sum formula    
    Cells(64, c).FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"    
    Cells(73, c).FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"    
    Cells(80, c).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
         
    'Clear grey tint in cell    
    Cells(55, c).Resize(25).Interior.ColorIndex = xlColorIndexNone         

    Application.ScreenUpdating = True    
End Sub

question from:https://stackoverflow.com/questions/65839608/dynamical-row-links-for-vba

Answer 1

Waitting for answers