Possible disregard formatting in exception match code?
 

Hi All,

I have the following code to compare two lists for the same record. If the
same record is found, it's highlighted (eg =
; both get highlighted) and works perfectly. However, if
the records are as follows: and , the code
doesn't match these two as equals because of the case changes.

I'm doing these as batch entries and it would be laborious to change to
lowercase all of the records. Is it possible to match regardless of
upper/lower case?

TIA!
Anders

sub exception_match ()

Dim oldupdate As Range, oldcell As Range

Dim newupdate As Range, newcell As Range

Dim Found As Boolean


Set oldupdate = Range("Table2")

Set newupdate = Range("Table1")

oldupdate.Interior.ColorIndex = xlColorIndexNone
newupdate.Interior.ColorIndex = xlColorIndexNone

For Each oldcell In oldupdate

Found = True

For Each newcell In newupdate

If oldcell.Value = newcell.Value Then

oldcell.Interior.ColorIndex = 6
newcell.Interior.ColorIndex = 6

Exit For

End If

Next

If Found Then

End If
Next

End Sub

Possible disregard formatting in exception match code?
 

Change this line
If oldcell.Value = newcell.Value Then

to

If UCase(Trim(oldcell.value)) = UCase(Trim(newcell.value)) Then

This will not only ignore case (by changing both to all uppercase, but will
also make sure a leading/trailing blank or two in either one is also ignored.


"Anders" wrote:

Hi All,

I have the following code to compare two lists for the same record. If the
same record is found, it's highlighted (eg =
; both get highlighted) and works perfectly. However, if
the records are as follows: and , the code
doesn't match these two as equals because of the case changes.

I'm doing these as batch entries and it would be laborious to change to
lowercase all of the records. Is it possible to match regardless of
upper/lower case?

TIA!
Anders

sub exception_match ()

Dim oldupdate As Range, oldcell As Range

Dim newupdate As Range, newcell As Range

Dim Found As Boolean


Set oldupdate = Range("Table2")

Set newupdate = Range("Table1")

oldupdate.Interior.ColorIndex = xlColorIndexNone
newupdate.Interior.ColorIndex = xlColorIndexNone

For Each oldcell In oldupdate

Found = True

For Each newcell In newupdate

If oldcell.Value = newcell.Value Then

oldcell.Interior.ColorIndex = 6
newcell.Interior.ColorIndex = 6

Exit For

End If

Next

If Found Then

End If
Next

End Sub

Possible disregard formatting in exception match code?
 

You could also use the StrComp function.
-or-
Adding "Option Compare Text" at the top of the module would work.
--
Jim Cone
Portland, Oregon USA