Change Sheet Name

I am trying to update my sheet name based on a reference to cell a27 in the worksheet. Only thing is I don't want it to affect all tabs, just those in between my bookends aptly named first and last. I have tried working this on my own, stringing bits of already existing macros in my book, but as I am a novice (that's being generous) it is clearly not working. Can someone take a look below and advise where I am going wrong? I know I really suck at this so don't laugh too hard.

' Keyboard Shortcut: Ctrl+n
Dim lngI As Long
For lngI = Sheets("First").Index + 1 To Sheets("Last").Index - 1 Step 1
    With Sheets(lngI)
      For i = 1 To Sheets.Count
    If Worksheets(i).Range("A27").Value <> "Current Invoice" Then
        Sheets(i).Name = Worksheets(i).Range("A27").Value
    End If
    
  Next lngI
    
End Sub

Also if anyone can offer any recommended reading on programming that would be most kind. Thanks a bunch.

Warmest Regards-
Kiki