VBA Spellcheck, highlight cell with misspelled word

Question

How can I alter my vba code to highlight the cell with spelling mistakes?

Tried adding Application.ScreenUpdating = True but didnt work.

Sub SpellCheckSheet()
Application.ScreenUpdating = True
    Dim xRg As Range
    Call unprotect_sheet
    Set xRg = ActiveSheet.Range("A:C")
    xRg.CheckSpelling
    Call protect_sheet
End Sub

Thanks.

Trying this, however when i cancel the spellchecker it doesnt stop the loop.

Sub SpellCheckSheet()

Dim cell As Range
With ActiveSheet
    .Unprotect ("123")
        For Each cell In Range("A:C")
            .CheckSpelling
        Next cell
    .Protect ("123")
End With
End Sub

IS there a way to identify UNPROTECTED cells within columns A:C and then, for each one, Activate/select that cell (so that you can see it on screen) and then activate Spell Check on that specific cell?

I don't think that you can do that in Excel because `checkSpelling` only runs the dialog window. It does not return anything. — Ike, Apr 08 '22 at 13:27
I see.. any chance I can highlight cells where checkspelling deems misspelled? or any other ideas? — Jonnyboi, Apr 08 '22 at 17:27
This Q is [answered here](https://stackoverflow.com/q/51263133/445425) — chris neilsen, Apr 11 '22 at 02:04
thanks. Ho wcan i convert `textToCheck = Range("A1")` to check more then the one cell? I get a type mismatch when I do A:C range. Or how can I get the dialogue box just to pop up on error'd cells? — Jonnyboi, Apr 11 '22 at 13:16

Pᴇʜ · Accepted Answer · 2022-04-11T14:47:10.710

Loop through your cells and spellcheck each cell.

Sub SpellCheckSheet()
    Dim cell As Range
    With ActiveSheet
        .Unprotect "123"
            For Each cell In Range("A:C")
                If Not Application.checkSpelling(cell) Then
                    'if spellcheck was not ok color it red
                    cell.Interior.Pattern = xlSolid
                    cell.Interior.Color = 255
                End If 
            Next cell
        .Protect "123"
    End With
End Sub

Note that For Each cell In Range("A:C") loops through all cells until the very end of the sheet which might take a long time because of all the empty cells.

So find the last used row

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

and limit it to the data only

For Each cell In Range("A:C").Resize(RowSize:=LastRow)

Workaround for cells longer than 255 characters

Split the long cells by space into words and spellcheck each word.

Sub SpellCheckSheet()
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim cell As Range
    With ActiveSheet
        For Each cell In Range("A:C").Resize(RowSize:=LastRow)
            Dim SpelledCorrectly As Boolean
            
            If Len(cell) > 0 Then  ' ignore empty cells
                If Len(cell) < 255 Then  ' spellcheck short texts at once
                    SpelledCorrectly = Application.CheckSpelling(cell)
                Else  ' split long texts into words and spellcheck each word
                    Dim Words() As String
                    Words = Split(cell)
                    
                    Dim Word As Variant
                    For Each Word In Words  ' loop through all words and spellcheck each word
                        SpelledCorrectly = Application.CheckSpelling(Word)
                        If Not SpelledCorrectly Then Exit For  ' if one word is wrong we can stop spellchecking and color the cell
                    Next Word
                End If
                
                If Not SpelledCorrectly Then
                    'if spellcheck was not ok color it red
                    cell.Interior.Pattern = xlSolid
                    cell.Interior.Color = 255
                End If
            End If
        Next cell
    End With
End Sub

Thanks Peh! Getting error on the `For Each cell In Range("A:C").Resize(RowSize:=LastRow)` part. `Application defined or object defined error` but also getting a type mismatch on your original answer on this line `If Not Application.CheckSpelling(cell) Then` I think it is because the cell is very long character count of 293 characters. — Jonnyboi, Apr 11 '22 at 14:29
@Jonnyboi see my edit. You can split the long cells with more than 255 characters into single words and spellcheck each word. Probably not as efficiant but works. — Pᴇʜ, Apr 11 '22 at 14:47

VBA Spellcheck, highlight cell with misspelled word

1 Answers1

Workaround for cells longer than 255 characters