Running a macro on cells that I have selected

Question

I need to create a Macro that only works on cells that I have selected.

I need to execute the below code for only cells that I have selected and not a range since the formatting will change frequently. Below is my code:

    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=XLOOKUP(RC[9],'[Region Name and Numbers.xlsx]Region Store'!C2,'[Region Name and Numbers.xlsx]Region Store'!C1,0,0)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B991")
    Range("B2:B991").Select
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=0
    Range("B2").Select
End Sub

Answer 1

Something like this?

Sub Test()

    With Selection
        .FormulaR1C1 = "=XLOOKUP(RC[9],'Sheet2'!C2,'Sheet2'!C1,0,0)"
        
        'You can't copy paste to non-continous ranges, so look at each area.
        Dim Area As Range
        For Each Area In .Areas
            Area.Copy
            Area.PasteSpecial Paste:=xlPasteValues
        Next Area
    End With
    
End Sub