Want to select a variable number of cells and enter values contained in their "connected" cells when clicking command button

[tattie]

Hi all

terrible Title I know but didn't know how better to explain it. It's actually very simple but unfortunately I am not yet good enough at this to work it out.

I have a "master table" set up with staff names in Column A and dates in Columns D - DF

Underneath I have the same staff names and dates, but with the rostered hours data in each relevant cell (i.e. per person / date).

What I am trying to do is have a command button that, when a cell or range of cells is highlighted in the "master" table, looks at the relevant date's rostered hours and enters this value in the master table.

So - e.g. Select F5 - click on button - macro looks up F325 and enters that value in F5.
Select H12 - M12 - click on button, macro looks up H332, I332, J332, K332....etc etc and enters those values in each relevant cell.

My current (extremely basic) code only picks up the first "connected cell" value and then duplicates this across the selected range. I know it's because I'm not using the correct keyword (i.e. ActiveCell) but don't know what do do instead.

Any help much appreciated as I am very stuck and short on time!!

Sub CommandButton1_Click()
'
' FullDayLeave Macro
' Inserts one day's full annual leave in the highlighted cell
' 
'
'
 
 
    With Selection.Interior
        .ColorIndex = 50
        .Pattern = xlSolid
    End With
    Selection.Font.ColorIndex = 50
    Selection.FormulaR1C1 = ActiveCell.Offset(302, 0)
 
End Sub