Selected Answer
Hello again MrEMann,
Personally I don't put a lot of faith in AI results, but that's just me.
Application.CheckSpelling checks the spelling of a complete word, it cannot check the spelling of the last 8 of 10 characters as you have suggested. Are the first 2 characters numbers and the characters 3 thru 10 spell a word?
Your code has a few errors (but you know that): 1) you have not declared the type of variable "substring" is; 2) myrange(3:10) should be myrange("3:10") but that tells VBA that "substring" is columns 3 thru 10.
Are you wanting to spellcheck just one column?
Assuming what you want to check is ##xxxxxxxx ( 2 numbers followed by an 8 letter word - 10 characters). Assuming you want to check these entries in a particular column.
I have created some code to do this. (see attached file).
Sub Check_2()
' macro written by WillieD24 for teachexcel.com Sept/2025
' declare variables
Dim LR As Long ' last used row of column
Dim RowNum As Long ' row number of cell being checked
Dim ColNum As Long ' number of column being checked
Dim ChkWrd As String ' the word being checked for spelling
Dim WordCnt As Long ' count of words mis-spelled
RowNum = 6 ' first row to be checked
ColNum = 3 ' column to be checked
LR = Cells(Rows.Count, ColNum).End(xlUp).Row
'MsgBox LR
Do Until RowNum > LR
Cells(RowNum, ColNum).Select
ChkWrd = Right(Cells(RowNum, ColNum), 8)
'MsgBox ChkWrd
If Application.CheckSpelling(ChkWrd) = False Then
With Selection
' MsgBox Application.CheckSpelling(Right(Cells(RowNum, ColNum), 8))
.Font.Color = vbWhite
.Interior.Color = vbBlack
WordCnt = WordCnt + 1
End With
End If
RowNum = RowNum + 1
Loop
Range("B2").Select
MsgBox WordCnt & " are words mis-spelled"
End Sub
'
Column "C", beginning withrow 6, has 15 random 8 character words with a 2-digit prefix. The code checks the spelling in all used cells.
If this solves things for you please mark my answer as Selected.
Cheers :-)
