Delete matches in 2 columns and highlight differences.

[MrVerm]

Hi all,

I've joined this forum seeking Excel VBA assistance. What I am trying to do is create a rudimentary spellcheck based on a word list.

There are 3 sheets in my excel file:
Source - Source text with various contents. Another piece of software delivers the content and column A and H are of interest.
Corrected - This is where the results of my script will show. Column A being the content from Source column A, the name of the variable. Column B is the content from Source column H the text in the variable (the incorrect text). Column C is the spellchecked version of column B, so a corrected version of the text in the variable from source.
Wordlist - Is a list of words with 2 columns. Column A being bad spelling. Column B corrected spelling.

So I have created a macro that creates the 3 sheets and delivers 3 columns in my corrected sheet by finding badly spelled words from Wordlist column A and replaces this word with the correct spelling into column C of corrected.
It compares words in the word list from the badly spelled column to content from column H. If it finds a match it fixes the error.

Sub Substitutions()

Dim rngData As Range
 Dim rngLookup As Range
 Dim Lookup As Range
 Dim SrcRng As Range

'copy the variable names to correction sheet.
With Worksheets("Source")
    Set SrcRng = .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With
Worksheets("Corrected").Range("A1").Resize(SrcRng.Rows.Count, 1).Value = SrcRng.Value

'copy column H to correction sheet (not corrected old version).
With Worksheets("Source")
    Set SrcRng = .Range(.Cells(1, "H"), .Cells(.Rows.Count, "H").End(xlUp))
End With
Worksheets("Corrected").Range("B1").Resize(SrcRng.Rows.Count, 1).Value = SrcRng.Value

'copy column H to correction sheet to column that will be corrrected based on content in wordlist.
With Worksheets("Source")
    Set SrcRng = .Range(.Cells(1, "H"), .Cells(.Rows.Count, "H").End(xlUp))
End With
Worksheets("Corrected").Range("C1").Resize(SrcRng.Rows.Count, 1).Value = SrcRng.Value

'reads the cells to correct.
With Sheets("Corrected")
 Set rngData = .Range("C1", .Range("C" & Rows.Count).End(xlUp))
 End With

'read "badly spelled" row in word list.
With Sheets("Wordlist")
 Set rngLookup = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
 End With

For Each Lookup In rngLookup
'Offset(0,1) determines that the value used is from column B on sheet2, the correct spelling.
'Each loop edits the content of the cells to be corrected to a new correct version.
 If Lookup.Value <> "" Then
 rngData.Replace What:=Lookup.Value, _
 Replacement:=Lookup.Offset(0, 1).Value, _
 LookAt:=xlPart, _
 SearchOrder:=xlByRows, _
 MatchCase:=False
 End If
 Next Lookup

End Sub

What I now want to do is delete all rows that are the same in the Corrected sheet, the rows where no spelling errors were found. To only have the rows left that had errors in the Corrected sheet. I'm using large files so comparing by hand would take way too long. The following step would be to compare the content for each row in column B and C of the Corrected sheet and highlight the difference. The spelling error is then clearly visible. This makes it easier to improve the text in the variables as the mistakes are clearly visible.

I'm hoping that the result allows me to input the data for my thesis into excel, run the macro and have only the rows (with variable names) left in sheet Corrected where mistakes were found, and that the mistakes found are highlighted. I can then use that information to improve my data.

However these 2 final steps are a bridge too far and I cannot get the deleting and highlighting to work.
Any input would be greatly appreciated.

 

[MrVerm]

Hi all,

If anyone can point me in the direction on how to create this working setup I can probably adapt the code myself to exactly fit my situation.
This might be a complicated question so to simplify what I want to do is have an excel file like this:

Sheet 1 - Column A variable name, Column B variable content with spelling mistakes.
Sheet 2 - Column A common spelling mistakes, Column B the same words but spelled correctly (fitting my analysis).
Sheet 3 - Column A variable name, Column B variable content with spelling mistakes (copy from sheet 1), Column C variable content (copied from sheet 1 but spelling checked using Column B on sheet 2).

The function will have to:
Copy the variable name from sheet 1 to sheet 3.
Copy the badly spelled content from sheet 1 to sheet 3.
Copy the badly spelled content from sheet 1 to sheet 3. Find spelling mistakes in this content using column A on sheet 2. Correct spelling mistakes using column B on sheet 2.
Delete all rows in sheet 3 where the original copy and spellchecked version are a exact match.
Highlight parts of the sentence in column C that are different from column B on sheet 3.

So for example

Sheet 1:

ID01	Chese is gret

ID02

Horse run

Sheet 2:

Chese	Cheese
Gret	Great

Hors

Horse

Sheet 3 (bottom row will be removed and not visible cause it is the same:

ID01

Chese is gret

Cheese is great

ID02

Horse run

Horse run

Any help would be greatly appreciated.