Automatic Spell Checker in Microsoft Excel 2010

If you are wondering how you can make Excel to spell check the data (typing error),

in this article, you will learn how to automatic spell check in Excel using VBA.
 
Let us understand with an example:

 

We have text contains in column A. Refer below snapshot
 
img1
 
In above snapshot; in cell A5 & A11 you will see there is spelling error.

The objective is to highlight the error in red color.
 
Click on Developer tab

From Code group, select Visual Basic
 
img2
 
or press ALT + F11 shortcut key to launch VB Editor screen.

Enter the following code in the current worksheet.

 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Myrange As Range

If Not Intersect(Target, Range(“A1″).CurrentRegion) Is Nothing Then

    For Each Myrange In Range(“A1″).CurrentRegion

      If Application.CheckSpelling(Myrange) = False Then

        Myrange.Font.Color = vbRed

      Else: Myrange.Font.Color = vbBlack

      End If

    Next

  End If

End Sub
 
img3
 
After writing the above code, the cells A5 & A11 will be turned into red color.
 
img4
 
The code works on the current region; if the text is in the current region then, the above VBA code will help the user to identify the error.

In case the user wants to highlight the error on specific cells then, we will use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Myrange As Range

If Not Intersect(Target, Range(“A1,B3,C5″)) Is Nothing Then

    For Each Myrange In Union([A1], [B3], [C5])

      If Application.CheckSpelling(Myrange) = False Then

        Myrange.Font.Color = vbRed

      Else: Myrange.Font.Color = vbBlack

      End If

    Next

  End If

End Sub
 
img5
 
Here, in above shown snapshot, the code has highlighted the error in cell B3 & C5; not in cell C1 & C7. This is because we want the code to check the cells A1, B3 & C5.
 
In this way, you can highlight the errors using VBA code.
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube