Updating existing VBA code to highlight cells

[hnt007]

Hello there! Thank you very much for helping me with this code. So I have this code to highlight words that are misspelled in red, but I would love to also have the cells that contain misspelled words turned "White, Background 1, 15% Darker" (see attached image).

Public Sub ThisOne()
Set MySheet = ActiveSheet

For Each MyCell In MySheet.Range("D8:D1000")
If MyCell.Value <> MyCell.Formula Then GoTo NextCell:
If Application.CheckSpelling(MyCell.Value) Then GoTo NextCell:

MySentence = " " & MyCell.Value
i = 2
While i < Len(MySentence)
If Mid(MySentence, i - 1, 1) = " " And Mid(MySentence, i, 1) <> "" Then
j = InStr(i, MySentence, " ") - 1
If j = -1 Then j = Len(MySentence)
MyWord = Mid(MySentence, i, j - i + 1)
If Not Application.CheckSpelling(MyWord) Then
With MyCell.Characters(Start:=i - 1, Length:=j - i + 1).Font
.Underline = xlUnderlineStyleNone
.Color = RGB(255, 0, 0)
End With
End If
i = j + 1
End If
i = i + 1
Wend

NextCell:
Next MyCell

End Sub

 

[Alex Blakenburg]

See if this gives you want you need:

MyCell.Characters(Start:=i - 1, Length:=j - i + 1).Interior.Color = RGB(217, 217, 217)

For more info see:
Microsoft Office Standard Colors: The Magic RGB Codes

 

[hnt007]

Un

See if this gives you want you need:

MyCell.Characters(Start:=i - 1, Length:=j - i + 1).Interior.Color = RGB(217, 217, 217)

For more info see:
Microsoft Office Standard Colors: The Magic RGB Codes

Unfortunately, I don't know where to put this code in my existing code so I can't get it to work :/

 

[Alex Blakenburg]

Between your End With and End If

 

[hnt007]

Between your End With and End If

Thank you, I get an error, attaching a screenshot

 

[hnt007]

Between your End With and End If

 

[bebo021999]

Are you trying to add font.color, not interior.color, aren't you?
replace "interior" with "font"

 

[hnt007]

Are you trying to add font.color, not interior.color, aren't you?
replace "interior" with "font"

Thanks for your help! Well I already have the code working to turn the font red when something is misspelled, I'm now trying to highlight the cell that countain that red font in gray. So if a word is misspelled, I'd have a gray cell with a red word in it

 

[Alex Blakenburg]

Oops sorry.
MyCell.Interior.Color = RGB(217, 217, 217)

 

[hnt007]

Oops sorry.
MyCell.Interior.Color = RGB(217, 217, 217)

Thanks for your help, unfortunately, the code has no error but the cell containing the misspelled word doesn't get a color