What happens with spell check?
First the spell check process will happen on current cell. The process will take each word in the cell for correct spelling.
If any of the words are incorrect, a pop up will appear displaying word suggestions as per the chosen language & dictionary.
Once done, it will move on to next word or next cell.
Process will continue till all the words in all the cells are processed.
How to activate Spell check in Excel?
In Excel, Spell check can be invoked using one of these easy steps;
- F7 – spell check in Excel keyboard shortcut
- Menu -> Review -> Spelling
- VBA Macro ‘.CheckSpelling’ method
First 2 methods are straight forward. The process will guide You thru further steps.
To have more control or automation in spell checking, option 3 can be used.
Excel VBA Macro Check Spelling?
The methods we are going to use here is ‘.CheckSpelling’ . How to use it?
It can be used on any cell, range, sheet or even a single word.
The below code illustrates each of the above mentioned categories for spell check.
Sub spell_check_excel_officetricks() Dim iRange As Range, wSh As Worksheet Dim sWord As String 'Spell Checking on a Cell Set iRange = ThisWorkbook.Sheets(1).Cells(1, 1) iRange.CheckSpelling 'Spell Checking on a Range Set iRange = ThisWorkbook.Sheets(1).Range("A1:C10") iRange.CheckSpelling 'Spell Checking on each Worksheet For Each wSh In ThisWorkbook.Sheets wSh.CheckSpelling Next 'Spell Checking on a word sWord = "namee" If Application.CheckSpelling(sWord) = False Then MsgBox "Word : " & sWord & " - Incorrect Spelling" End If End Sub
Note that there are 2 variants of this functions.
When it is invoked using Excel object, it works on the selected range of cells.
When it is invoked with application, You have to pass the word that needs to be checked.
Highlight Cell with Incorrect Spelling
Here is a small utility VBA code, that checks for spelling in whole worksheet.
Then it highlights the cell with any incorrect spelled word with a color (For example: cell background changes to Yellow)
Lets get the code for this highlighting part.
Sub hilight_spelling_mistakes_officetricks() Dim iRange As Range, wSh As Worksheet Dim sWord As String 'Spell Checking on each Worksheet For Each wSh In ThisWorkbook.Sheets 'Spell Checking on a word For Each iRange In wSh.UsedRange iRange.Interior.Color = xlNone sWord = iRange.Text If Application.CheckSpelling(sWord) = False Then iRange.Interior.Color = vbYellow MsgBox "Word :" & sWord & " - Incorrect Spelling" End If Next Next End Sub
If there are too many spelling mistakes, You can remove the msgbox inside the interior loop.
It would be annoying to have too many messages popup every time.
Press F5 to run this macro. Excel will highlight all the cells with Yellow background color which are having any spelling mistakes.