I recently responded to an interesting post where someone asked for assistance with some code to check spelling for a words in specific cells. The poster wanted the cells with the errors to have the font highlighted in red if the word was spelt incorrectly. Here is the code I came up with.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Myrange As Range
If Not Intersect(Target, Range("L10,L14,L29")) Is Nothing Then
   For Each Myrange In Union([L10], [L14], [L29])
      If Application.CheckSpelling(Myrange) = False Then
         Myrange.Font.Color = vbRed
      Else: Myrange.Font.Color = vbBlack
      End If
   Next
End If
End Sub

As it turned out the person wanted something a bit more complex. They wanted the whole sheet to be checked and when it was checked to highlight only the words spelt incorrectly. So if multiple words were spelt incorrectly then only highlight the words in a specific cell which were wrong. Enter my friend Narayan (who is also an Excel Ninja on Chandoo’s site) he came up with the following which I have altered only slightly.

Private Sub Worksheet_Change(ByVal Target As Range)
Call CheckSpelling1(Target)
End Sub

Sub CheckSpelling1(r As Range)
Dim rng As Range
Dim ar() As String
Dim i As Long
Dim j As Long
With Application.SpellingOptions
   .IgnoreCaps = True
   .IgnoreFileNames = True
   .IgnoreMixedDigits = True
End With

   For Each rng In r
      If Not rng.HasFormula And VarType(rng.Value) = vbString Then
         ar = Split(Replace(rng.Value, Chr(160), " "), " ")
         j = 1
         rng.Font.ColorIndex = xlColorIndexAutomatic
         For i = 0 To UBound(ar)
            If Not Application.CheckSpelling(ar(i)) Then
               rng.Characters(j, Len(ar(i))).Font.ColorIndex = 3
            End If
            j = j + 1 + Len(ar(i))
         Next i
      End If
   Next rng
End Sub