Comparing cell string values between 2 columns in VBA

[nandujo]

Hello,

I'm trying to put a command in VBA that will check to see if the cell value in column A and B have the exact same string. Basically, comparing A2 <-> B2, A3 <-> B3 <->A4 <-> B4... etc all the way down the entire column.

Then when it see the strings are not exact, to highlight it (see below).

So far I got:

With Sheets("Audit")
Dim Ary As Variant
Dim Cl As Range
Ary = Split(Range("'A:A"))
For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(1, Cl, Ary(0), vbTextCompare) = 0 And InStr(1, Cl, Ary(UBound(Ary)), vbTextCompare) = 0 Then
Cl.Interior.Color = 13551615
End If
Next Cl
End With

Which isn't working... Any suggestions? I feel like it's something basic i'm missing.

 

[Fluff]

Why not just use conditional formatting?
Select B2 to tne of data & use this formula
=$B2<>$A2

 

[Joe4]

Instead of reinventing the wheel here, why not just use Excel's Conditional Formatting functionality, which can do exactly this rather easily without any need for VBA code.
If it is part of a larger VBA project, you can have your VBA code set-up the Conditional Formatting (you can get a lot of the code you need to do that by turning on the Macro Recorder and recording yourself doing this manually).

 

[nandujo]

Much thanks for the tips!!

I was able to get the unique values checked and knew what to put down using the marco recorder... however though, it doesn't seem to check the duplicates correctly.

Here's what I want the project to check:

Basically checking by any unique values by row. I was able to get the below code to check the values, but it checks the range as a whole...

Range("A2:H1529").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlUnique
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Any suggestions?
Much thanks again!!

 

[Fluff]

Select A2:D1529 and use this formula
=A2<>E2

 

[nandujo]

Select A2:D1529 and use this formula
=A2<>E2

Got it! Thanks again for your help! Here's the result coding:

Range("A2:D1529").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A2<>E2"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

 

[Fluff]

Are you just running the code, once on a particular, or multiple times?

 

[nandujo]

It more like once on a month... checking how the values have changed during that time

 

[Fluff]

Ok, it's just that you do not appear to be deleting the Conditional Formatting in the code, so they will build up over time which can potentially slow your workbook down to a crawl.