Spell Check within a cell and change color of misspelled words to red

Question

I'm working on an automation, where i need to do spell check a paragraph within a cell and highlight error words in red, and this spell check i need to do in column which will have dynamic range.

I'm using the below code

Sub spellcheck()

    Dim intOffsetCol As Integer
    Dim intChrCnt As Integer
    Dim varTempString As Variant

    intOffsetCol = 1

        For intChrCnt = 1 To Trim(Len(ActiveCell.Value)) Step 1 'Left to right
            'A space character is represented by an ASCII code of 32.
            If Asc(Mid(ActiveCell.Value, intChrCnt, 1)) <> 32 Then
                varTempString = varTempString & Mid(ActiveCell.Value, intChrCnt, 1)
            Else


                If Not Application.CheckSpelling(Word:=varTempString) Then
                varTempString.Interior.ColorIndex = 52

                varTempString = ""
                intOffsetCol = intOffsetCol + 1
                End If
            End If
        Next intChrCnt

        If varTempString <> "" Then
            If Not Application.CheckSpelling(Word:=varTempString) Then varTempString.Interior.ColorIndex = vbRed

        End If

End Sub

And I'm getting "Run time error 424" on varTempString.Interior.ColorIndex = 52

Why am I getting an error on that line?

Answer 1

varTempString is being used as a String variable and your code is attempting to change the cell interior which is impossible.

The solution to highlighting specific text is:

Sub SpellCheckUpdate()

Dim cel As Range, CellLen As Long, CurChr As Long, TheString As String

For Each cel In Selection
    For CurChr = 1 To Len(cel.Value)
        If Asc(Mid(cel.Value, CurChr, 1)) = 32 Then
            If InStr(CurChr + 1, cel.Value, " ") = 0 Then
                TheString = Mid(cel.Value, CurChr + 1, Len(cel.Value) - CurChr)
            Else
                TheString = Mid(cel.Value, CurChr + 1, InStr(CurChr + 1, cel.Value, " ") - CurChr)
            End If
            If Not Application.CheckSpelling(Word:=TheString) Then
                cel.Characters(CurChr + 1, Len(TheString)).Font.Color = RGB(255, 0, 0)
            Else
                cel.Characters(CurChr + 1, Len(TheString)).Font.Color = RGB(0, 0, 0)
            End If
            TheString = ""
        End If
    Next CurChr
Next cel

End Sub

This is much simpler code to highlight a FULL cell with a misspelled word:

Sub ColorMispelledCells()  
    For Each cl In ActiveSheet.UsedRange  
        If Not Application.CheckSpelling(Word:=cl.Text) Then _  
          cl.Interior.ColorIndex = 28  
    Next cl  
End Sub