VBA Spellcheck, highlight cell with misspelled word

user_6346514 0
Viewed 156

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?

vba excel
1 Answers

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
user_32196130
Related