I'm writing a macro to spell check each word in a cell and simply change the color of misspelled words. The defualt delimiter is Chr(32), which is a space, but I'd like to also use Chr(10), line break, as our people often use soft breaks. Without the Chr(10) incorporated, if a misspelled word is at the beginning or end of a line, the correctly spelled word adjacent to it, if only separated by a line break, will also be highlighted. Here is my code:
Sub Word_By_Word()
Dim oRange As Excel.Range
Set oRange = ActiveCell
Application.ScreenUpdating = True
Dim lCellHighlightColor As Long
lCellHighlightColor = xlNone
Dim lWordHighlightColor As Long
lWordHighlightColor = vbRed
On Error GoTo 0
Dim oCell As Object
Dim iLastRowProcessed As Integer
iLastRowProcessed = 0
For Each oCell In oRange
If ((bColumnMarker = True) And _
(iLastRowProcessed <> oCell.Row)) Then
iLastRowProcessed = oCell.Row
Cells(oCell.Row, iColumnToMark) = ""
End If
Rows(oCell.Row).Select
Dim bResultCell As Boolean
bResultCell = True
If (Len(oCell.Text) < 256) Then
bResultCell = Application.CheckSpelling(oCell.Text)
End If
Dim iTrackCharPos As Integer
iTrackCharPos = 1
Dim vWords As Variant
vWords = Split(oCell.Text, Chr(32), -1, vbBinaryCompare)
Dim i As Integer
For i = LBound(vWords) To UBound(vWords)
Dim iWordLen As Integer
iWordLen = Len(vWords(i))
Dim bResultWord As Boolean
bResultWord = Application.CheckSpelling(Word:=vWords(i))
If (bResultWord = False) Then
If (iWordLen > 1) Then
Dim iWL As Integer
For iWL = iWordLen To 1 Step -1
If (Not (Mid(vWords(i), iWL, 1) Like "[0-9A-Za-z]")) Then
vWords(i) = Left(vWords(i), (iWL - 1))
Else
Exit For
End If
Next iWL
If (Mid(vWords(i), iWL, 1) = "s") Then
vWords(i) = Left(vWords(i), (iWL - 1))
End If
bResultWord = Application.CheckSpelling(Word:=vWords(i))
End If
End If
If (bResultWord = True) Then
If ((Len(vWords(i)) > 0) And (vWords(i) = UCase(vWords(i)))) Then
Dim iHyphenPos As Integer
iHyphenPos = InStr(1, vWords(i), "-")
If (iHyphenPos > 0) Then
Dim vHyphenates As Variant
vHyphenates = Split(LCase(vWords(i)), "-", -1, vbBinaryCompare)
Dim iH As Integer
For iH = LBound(vHyphenates) To UBound(vHyphenates)
bResultWord = Application.CheckSpelling(Word:=vHyphenates(iH))
If (bResultWord = False) Then
Exit For
End If
Next iH
End If
End If
End If
If (bResultWord = False) Then
bResultCell = False
oCell.Characters(iTrackCharPos, iWordLen).Font.Bold = False
oCell.Characters(iTrackCharPos, iWordLen).Font.Color = lWordHighlightColor
Else
oCell.Characters(iTrackCharPos, iWordLen).Font.Bold = False
oCell.Characters(iTrackCharPos, iWordLen).Font.Color = vbBlack
End If
iTrackCharPos = iTrackCharPos + iWordLen + 1
Next i
If (bResultCell = True) Then
oCell.Interior.ColorIndex = xlColorIndexNone
Else
oCell.Interior.Color = lCellHighlightColor
If (bColumnMarker = True) Then
Cells(oCell.Row, iColumnToMark) = sMarkerText
End If
End If
Next oCell
End Sub
I highlighted the split command in red, which is where I need to incorporate the space and line breaks as delimiters. Any help would be great!
Bookmarks