JeffJ60
04-24-2015, 11:52 AM
Good afternoon!
There are several versions of text comparison code on this excellent forum, but those I have discovered so far seems to identify differences based on the position (order) of the characters or words in the strings. It seems like multiple occurences are also not always handled the same way (sometimes when a word repeats inside a string only the first occurence is handled.)
I am trying to achieve these considerations;
Highlight the differences in text in two columns in red.
Case insensitive.
Operates at the word level, not the character level.
Word position in the string not considered.
Word frequency not considered - all instances of common words remain in black. Notice how the words "the" occurs twice in one cell and once in the other, but neither case is identified as a variance (made red).
Here's an example of a column A and column B and the desired output. Pretty straight forward, but please ask if there are questions!
13247
Thanks in advance for any direction you could provide! My starting point has been this code (which is awesome already - I attribute it to Mike Rickson, but may be off there;
Sub CheckAgainstColumnA()
Dim CSensitivity As Long
Dim oneCell As Range
Select Case MsgBox("Case Sensitive", vbYesNo)
Case Is = vbCancel
Exit Sub
Case Is = vbYes
CSensitivity = 0
Case Is = vbNo
CSensitivity = 1
End Select
With ThisWorkbook.Sheets("sheet1").Range("A1:A1000"): Rem adjust
For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
oneCell.Font.Color = redCIndex
oneCell.Offset(0, 1).Font.ColorIndex = blackCIndex
Call highlightDifference(oneCell, oneCell.Offset(0, 1), CSensitivity)
Call highlightDifference(oneCell.Offset(0, 1), oneCell, CSensitivity)
Next oneCell
End With
End Sub
Sub highlightDifference(refCell As Range, testCell As Range, Optional CaseSensitivity As Long)
Rem default caseSenstivity = 0 For Case insensitive, Set CaseSensitivity = 1
Dim refString As String, testString As String
Dim i As Long, startPoint As Long, newPoint As Long
CaseSensitivity = Sgn(CaseSensitivity) ^ 2
With testCell.Font
.ColorIndex = redCIndex
.FontStyle = "Bold"
End With
refString = refCell.Text
testString = testCell.Text
startPoint = 1
For i = 1 To Len(refString)
newPoint = InStr(startPoint, testString, Mid(refString, i, 1), CaseSensitivity)
If newPoint <> 0 Then
With testCell.Characters(newPoint, 1).Font
.ColorIndex = blackCIndex
.FontStyle = "Regular"
End With
startPoint = newPoint + 1
End If
Next i
End Sub
There are several versions of text comparison code on this excellent forum, but those I have discovered so far seems to identify differences based on the position (order) of the characters or words in the strings. It seems like multiple occurences are also not always handled the same way (sometimes when a word repeats inside a string only the first occurence is handled.)
I am trying to achieve these considerations;
Highlight the differences in text in two columns in red.
Case insensitive.
Operates at the word level, not the character level.
Word position in the string not considered.
Word frequency not considered - all instances of common words remain in black. Notice how the words "the" occurs twice in one cell and once in the other, but neither case is identified as a variance (made red).
Here's an example of a column A and column B and the desired output. Pretty straight forward, but please ask if there are questions!
13247
Thanks in advance for any direction you could provide! My starting point has been this code (which is awesome already - I attribute it to Mike Rickson, but may be off there;
Sub CheckAgainstColumnA()
Dim CSensitivity As Long
Dim oneCell As Range
Select Case MsgBox("Case Sensitive", vbYesNo)
Case Is = vbCancel
Exit Sub
Case Is = vbYes
CSensitivity = 0
Case Is = vbNo
CSensitivity = 1
End Select
With ThisWorkbook.Sheets("sheet1").Range("A1:A1000"): Rem adjust
For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
oneCell.Font.Color = redCIndex
oneCell.Offset(0, 1).Font.ColorIndex = blackCIndex
Call highlightDifference(oneCell, oneCell.Offset(0, 1), CSensitivity)
Call highlightDifference(oneCell.Offset(0, 1), oneCell, CSensitivity)
Next oneCell
End With
End Sub
Sub highlightDifference(refCell As Range, testCell As Range, Optional CaseSensitivity As Long)
Rem default caseSenstivity = 0 For Case insensitive, Set CaseSensitivity = 1
Dim refString As String, testString As String
Dim i As Long, startPoint As Long, newPoint As Long
CaseSensitivity = Sgn(CaseSensitivity) ^ 2
With testCell.Font
.ColorIndex = redCIndex
.FontStyle = "Bold"
End With
refString = refCell.Text
testString = testCell.Text
startPoint = 1
For i = 1 To Len(refString)
newPoint = InStr(startPoint, testString, Mid(refString, i, 1), CaseSensitivity)
If newPoint <> 0 Then
With testCell.Characters(newPoint, 1).Font
.ColorIndex = blackCIndex
.FontStyle = "Regular"
End With
startPoint = newPoint + 1
End If
Next i
End Sub