CODEWITHSUNDEEP
vbaexcel
Amanzaloko
Amanzaloko

Reputation: 13

Application.CheckSpelling Fails on Cells with > 255 Chars

I'm trying to write a macro that will highlight any cells in the range e5:e20000 that have a spelling error. I get a "runtime error 113" every time I hit a cell with more than 255 characters in it. I tried adding a condition to skip +255 char cells, but it's not really working.

Ideally, I want to include all cells, regardless of the character number. Any advice? Thanks!

Sub Cellswithtypos()
For Each cl In ActiveSheet.UsedRange.Range("E5:E20000").Cells
    If Len(cl.Value) <= 255 And Not Application.CheckSpelling(Word:=cl.Text) Then _
cl.Interior.ColorIndex = 18
Next cl
End Sub

Upvotes: 0

Views: 752

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

You have to nest to check the length of cell first. I also added in a check for blank cells so you can bypass blanks (should speed up code).

Sub Cellswithtypos()
For Each cl In ActiveSheet.UsedRange.Range("E5:E20000").Cells
    If Len(cl.Value) <= 255 And Len(cl.Value) > 0 Then
        If Not Application.CheckSpelling(Word:=cl.Text) Then
            cl.Interior.ColorIndex = 18
        End If
    End If
Next cl
End Sub

Upvotes: 1

Related Questions