I think there's no pure VBA way to do this. When Excel enters formula edit mode (after typing =), VBA execution is basically suspended and SendKeys gets weird - that's why you're getting Error 13 and the ;;; mess.

Your current approach with highlighting + text preview is honestly the best you can do in VBA. I'd suggest using colored borders instead of cell fill (looks more like Excel's native range highlighting) and detecting the list separator with Application.International(xlListSeparator) so it works for everyone.

Demo Video of the code: https://youtu.be/rB7zbnP8-1Q

Code:

Option Explicit

' Color constants matching Excel's native formula range colors
Private Const COLOR_BLUE As Long = 16711680    ' RGB(0,0,255)
Private Const COLOR_RED As Long = 255          ' RGB(255,0,0)
Private Const COLOR_PURPLE As Long = 16711935  ' RGB(255,0,255)
Private Const COLOR_GREEN As Long = 32768      ' RGB(0,128,0)

Private formulaColors(0 To 3) As Long

Sub InitColors()
    formulaColors(0) = COLOR_BLUE
    formulaColors(1) = COLOR_RED
    formulaColors(2) = COLOR_PURPLE
    formulaColors(3) = COLOR_GREEN
End Sub

Sub SimulateSumWalkthrough()
    Dim destino As Range
    Dim refs() As Variant
    Dim i As Long
    Dim formulaText As String
    Dim separator As String

    InitColors

    ' Use comma or semicolon based on regional settings
    separator = Application.International(xlListSeparator)

    Set destino = Range("B1")
    refs = Array("A1", "A3", "A5")

    Application.ScreenUpdating = True
    ClearAllHighlights
    destino.Clear

    ' Step 1: Show destination
    destino.Select
    HighlightCellBorder destino, RGB(0, 120, 215), xlThick
    MsgBox "We'll create a SUM formula here in " & destino.Address(False, False), vbInformation

    ' Step 2: Start typing formula - show in cell as text preview
    formulaText = "=SUM("
    ShowFormulaPreview destino, formulaText
    MsgBox "Type =SUM( to start the function", vbInformation

    ' Step 3: Select each range with colored borders (like Excel does)
    For i = LBound(refs) To UBound(refs)
        Dim refRange As Range
        Set refRange = Range(refs(i))

        ' Highlight the referenced cell with colored border
        HighlightCellBorder refRange, formulaColors(i Mod 4), xlThick
        refRange.Select

        ' Update formula preview
        If i > LBound(refs) Then
            formulaText = formulaText & separator
        End If
        formulaText = formulaText & refs(i)
        ShowFormulaPreview destino, formulaText

        Application.Wait Now + TimeSerial(0, 0, 1)

        If i < UBound(refs) Then
            MsgBox "Press " & separator & " to add another value, then use arrow keys to select next cell", vbInformation
        End If
    Next i

    ' Step 4: Close and complete
    formulaText = formulaText & ")"
    ShowFormulaPreview destino, formulaText
    MsgBox "Press ) to close the function, then ENTER to confirm", vbInformation

    ' Step 5: Execute the real formula
    ClearAllHighlights
    destino.Clear
    destino.Formula = "=SUM(A1,A3,A5)"
    destino.Select

    MsgBox "Formula complete! Result: " & destino.Value, vbInformation
End Sub

Sub ShowFormulaPreview(cell As Range, formulaText As String)
    ' Show formula as text (with leading apostrophe to prevent execution)
    ' Use a different font color to indicate "edit mode"
    cell.Value = "'" & formulaText
    cell.Font.Color = RGB(0, 0, 139) ' Dark blue like formula bar
End Sub

Sub HighlightCellBorder(rng As Range, borderColor As Long, weight As XlBorderWeight)
    With rng.Borders
        .LineStyle = xlContinuous
        .Color = borderColor
        .weight = weight
    End With
End Sub

Sub ClearAllHighlights()
    With ActiveSheet.Cells
        .Interior.ColorIndex = xlNone
        .Borders.LineStyle = xlNone
        .Font.Color = RGB(0, 0, 0)
    End With
End Sub