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

Question

如何更改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中未受保护的单元格，然后对每个单元格激活/选择该单元格(这样你就可以在屏幕上看到它(，然后在该特定单元格上激活拼写检查？

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