$$Excel-Macros$$ Spell Check and Highlighting Individual Words
Hi I have an Excel sheet with VBA which is designed to compare two boxes of text/copy and highlight any differences. The existing code is very good although, it does not currently highlight individual words (it only changes the box color to red to suggest there is an error). Here is the existing code (the spreadsheet can also be shared if that would be useful?): 'check the spelling of the selected text Select Case bcheck Case True If SpellingIsCorrect(sCopy) = False Then If .Range(Q plRow).Value = Then .Range(Q plRow).Value = Check ASFspelling Else .Range(Q plRow).Value = .Range(Q plRow).Value | Check ASF spelling End If End If If SpellingIsCorrect(sEmail) = False Then If .Range(Q plRow).Value = Then .Range(Q plRow).Value = Check email spelling Else .Range(Q plRow).Value = .Range(Q plRow).Value | Check email spelling End If .Range(J plRow).Select 'background = red asthis is an error With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.399945066682943 .PatternTintAndShade = 0 End With End If Case Else 'do nothing as we do not want to check the spelling in anything but the above sections End Select Is it possible to adapt the code so that individual (incorrectly spelt) words are highlighted (rather than the whole block)? Any help much appreciated, Kind regards Greg -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Spell Check and Highlighting Individual Words
Hi Try this .. Hope it will Resolve Your Query *Public MyTest As Boolean Public myWord As String* *Sub GetText()* *On Error Resume Next* *Dim ws As Worksheet* *Dim st As String* *Dim arr() As String* *Dim cell As Range* ** *Set ws = ActiveSheet* *Sheets(WrongWord).Delete* *Sheets.Add.Name = WrongWord* *ws.Activate* *st = * ** ** *For Each cell In ActiveSheet.UsedRange* *st = st cell.Value* *Next* ** *arr = Split(st, )* *For i = LBound(arr) To UBound(arr)* *myWord = arr(i)* *Call mySpell* *If MyTest = False Then Sheets(WrongWord).Range(A Sheets(WrongWord).Range(A2).End(xlUp).Row + 1).Value = myWord* *MyTest = False* *Next* *Sheets(WrongWord).Activate* *MsgBox Total WorksheetFunction.CountA(ActiveSheet.Cells)Wrong Word Found in Data , vbInformation* *End Sub* *Sub mySpell()* *MyTest = Application.CheckSpelling(myWord)* * * *End Sub* http://excelpoweruser.blogspot.com/2011/07/get-wrong-keyword-on-another-sheets.html On Thu, Oct 13, 2011 at 4:41 PM, Greg2011 gdbar...@googlemail.com wrote: Hi I have an Excel sheet with VBA which is designed to compare two boxes of text/copy and highlight any differences. The existing code is very good although, it does not currently highlight individual words (it only changes the box color to red to suggest there is an error). Here is the existing code (the spreadsheet can also be shared if that would be useful?): 'check the spelling of the selected text Select Case bcheck Case True If SpellingIsCorrect(sCopy) = False Then If .Range(Q plRow).Value = Then .Range(Q plRow).Value = Check ASFspelling Else .Range(Q plRow).Value = .Range(Q plRow).Value | Check ASF spelling End If End If If SpellingIsCorrect(sEmail) = False Then If .Range(Q plRow).Value = Then .Range(Q plRow).Value = Check email spelling Else .Range(Q plRow).Value = .Range(Q plRow).Value | Check email spelling End If .Range(J plRow).Select 'background = red asthis is an error With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.399945066682943 .PatternTintAndShade = 0 End With End If Case Else 'do nothing as we do not want to check the spelling in anything but the above sections End Select Is it possible to adapt the code so that individual (incorrectly spelt) words are highlighted (rather than the whole block)? Any help much appreciated, Kind regards Greg -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Rajan verma +91 9158998701 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Spell Check and Highlighting Individual Words
Hi Rajan Thanks so much for your speedy response. I've added the code to my complete VBA but, it is not working. Could I send the workbook to you for review? (it's not clear how to attach to this thread??) I am still very much an amateur with regards to VB! Kind regards Greg On Oct 13, 2:53 pm, rajan verma rajanverma1...@gmail.com wrote: Hi Try this .. Hope it will Resolve Your Query *Public MyTest As Boolean Public myWord As String* *Sub GetText()* *On Error Resume Next* * Dim ws As Worksheet* * Dim st As String* * Dim arr() As String* * Dim cell As Range* * * * Set ws = ActiveSheet* * Sheets(WrongWord).Delete* * Sheets.Add.Name = WrongWord* * ws.Activate* * st = * * * * * * For Each cell In ActiveSheet.UsedRange* * st = st cell.Value* * Next* * * * arr = Split(st, )* * For i = LBound(arr) To UBound(arr)* * myWord = arr(i)* * Call mySpell* * If MyTest = False Then Sheets(WrongWord).Range(A Sheets(WrongWord).Range(A2).End(xlUp).Row + 1).Value = myWord* * MyTest = False* * Next* * Sheets(WrongWord).Activate* * MsgBox Total WorksheetFunction.CountA(ActiveSheet.Cells) Wrong Word Found in Data , vbInformation* *End Sub* *Sub mySpell()* *MyTest = Application.CheckSpelling(myWord)* * * *End Sub*http://excelpoweruser.blogspot.com/2011/07/get-wrong-keyword-on-anoth... On Thu, Oct 13, 2011 at 4:41 PM, Greg2011 gdbar...@googlemail.com wrote: Hi I have an Excel sheet with VBA which is designed to compare two boxes of text/copy and highlight any differences. The existing code is very good although, it does not currently highlight individual words (it only changes the box color to red to suggest there is an error). Here is the existing code (the spreadsheet can also be shared if that would be useful?): 'check the spelling of the selected text Select Case bcheck Case True If SpellingIsCorrect(sCopy) = False Then If .Range(Q plRow).Value = Then .Range(Q plRow).Value = Check ASFspelling Else .Range(Q plRow).Value = .Range(Q plRow).Value | Check ASF spelling End If End If If SpellingIsCorrect(sEmail) = False Then If .Range(Q plRow).Value = Then .Range(Q plRow).Value = Check email spelling Else .Range(Q plRow).Value = .Range(Q plRow).Value | Check email spelling End If .Range(J plRow).Select 'background = red asthis is an error With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.399945066682943 .PatternTintAndShade = 0 End With End If Case Else 'do nothing as we do not want to check the spelling in anything but the above sections End Select Is it possible to adapt the code so that individual (incorrectly spelt) words are highlighted (rather than the whole block)? Any help much appreciated, Kind regards Greg -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- Regards Rajan verma +91 9158998701 -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link