VBA Spellcheck, highlight cell with misspelled word-CodePudding

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?

CodePudding user response：

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