excel - VBA拼写检查，突出显示拼写错误的单元格

Question

这个问题在这里已经有了答案:





Ignore Text Boxes on Spellcheck

(1 个回答)


3个月前关闭。




如何更改我的 vba 代码以突出显示拼写错误的单元格？
尝试添加 Application.ScreenUpdating = True但没有用。

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

谢谢。
尝试这个，但是当我取消拼写检查时它不会停止循环。

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

有没有办法在 A:C 列中识别未 protected 单元格，然后为每个单元格激活/选择该单元格(以便您可以在屏幕上看到它)，然后在该特定单元格上激活拼写检查？

Answer 1

循环遍历您的单元格并对每个单元格进行拼写检查。

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

请注意 For Each cell In Range("A:C")循环遍历所有单元格，直到工作表的最后，由于所有空单元格，这可能需要很长时间。
所以找到最后使用的行

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

并将其限制为仅数据

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

超过 255 个字符的单元格的解决方法
按空格将长单元格拆分为单词并拼写检查每个单词。

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