Excel doesn't do as good of a job as some text editors when it comes to spellchecking, and we know how frustrating this can be. Let us show you how to highlight misspelled cells in Excel using VBA.
Spelling check feature has been a staple in Microsoft Office products for a long time. However, this can be a bit tricky to use in Excel. CheckSpelling method under the Application object returns a Boolean value based on the value of a specified text. We can use a For Each…Next loop to check every cell in a worksheet. A simple If condition is enough to use the Boolean value that returns from the CheckSpelling method.
After you get the range, you can use the ColorIndex property to highlight that range. Here are some index numbers for colors:
- 3: Red
- 5: Blue
- 6: Yellow
- 0: No Fill
We suggest you to use On Error Resume Next at the start of your code to account for worksheets that don't have any cells with errors.
Before continuing the codes here is a simple explanation about them. Firstly; you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
Highlighting misspelled cells
Sub HighlightMissspelledCells() Dim rng As Range For Each rng In ActiveSheet.UsedRange If Not Application.CheckSpelling(rng.Text) Then rng.Interior.ColorIndex = 6 End If Next rng End Sub