Highlighting wrongly spelled words in VBA

 Hello Marleen and welcome to the forum,

Need a bit more information. Are you wanting to highlight misspellings in VBA code or in a worksheet using VBA? It is not possible to apply conditional formatting to VBA code, but you can use VBA code to apply conditional formatting to a range in a worksheet. Bear in mind that you need to specify the range which the conditional formatting rule applies. However, there is no conditional formatting rule to check spelling.

You could use some simple VBA code like the following to check spelling. It specifies the range to check and if a misspelling is found it throws up the "Spell Check" window allowing the user to select the spelling to use.

    Range("A1:E50").Select   ' change range to suit
    Selection.CheckSpelling SpellLang:=4105   ' 4105 is English
    Range("A1").Select

Hope this helps,

Cheers   :-)

Hi Marleen

It's not so easy to do that in Excel (e.g. Willie's suggestion throws up the Spell Chell requestor each time) but it is possible if you also have Word...

In the attched file, you'll see a few cells with deliberate typos plus a green button labelled "Highlight...". If you click that, it launches this procedure (in VBA Module1), commented so you can see what's happening:

Dim Wrd As Object

Sub SpellColour()

    Dim Cll As Range, i As Long

    'clear previous results
    ActiveSheet.UsedRange.Cells.ClearFormats
    ' loop through used range
    For Each Cll In ActiveSheet.UsedRange.Cells
        ' spell check if there's anything in the cell
        If Len(Cll.Text) <> 0 Then
            'call function to check and colour cell yellow if mistake(s)
             If WrdSpllChck(Cll.Text) = False Then
                Cll.Interior.Color = vbYellow
                ' add to counter
                i = i + 1
            End If

        End If
    Next Cll
    ' close Word (if running)
    If Not Wrd Is Nothing Then
        Wrd.Quit
        Set Wrd = Nothing
    End If

    ' tell user
    MsgBox "Spell check failed in " & i & " cells (now shaded yellow)"

End Sub

Note the first line "declares" a variable which is used in that and the function below- which is called by the line in bold above.

Function WrdSpllChck(s As String) As Boolean
    ' launch Word if not running
    If Wrd Is Nothing Then
        Set Wrd = CreateObject("Word.Application")
        Wrd.Visible = False
    End If
    ' return True or False, based on if SpellChecker finds errors
    WrdSpllChck = Wrd.CheckSpelling(s)

End Function

The nett effect is that it will shade cells yellow if Word's spell check finds errors in those cells. It isn't conditional formatting (as mentioned in your Question) but does point out the suspect cells. 

You can have text in as many cells as you like but it might slow things down if there's lots. It opens an instance of Word (invisibly) if text is found and skips running the function for empty cells to speed matters up. It closes that instance at the end.

Hope this is what you want- if so, please mark this Answer as Selected.