PDA

View Full Version : Excel VBA Help: Checking String Values of Two Columns for Equality



contramonk
06-02-2015, 06:48 PM
I'm a noob at VBA and I am trying to solve a macro problem that I thought would be easy.

I want to compare the string values of two columns to each other. If they are not equal I would like to highlight the row. Here is my feeble attempt:


Sub EqualityCheck()
For Each c In Worksheets(1).Range("A").Cells
If c.Value <> Range("B").Value Then...
Next
End Sub

However I keep getting an error (which is not surprising). Run-time error '1004': Application-defined or object-defined error. I am imagining this is because VBA doesn't like it when I refer to the column as "A".
I am sure that line 3 also fails, because the loop will not know which row in column B to check.

Any help is greatly appreciated, I've searched and searched for an answer that would help me with this problem. No luck.

Aussiebear
06-02-2015, 08:20 PM
Try the following


Option Explicit
Sub strCompDemo()
Dim iComp As Integer, i As Integer
Dim str1 As String, str2 As String

For i = 1To 200
str1 = Range("A" & i)
str2 = Range(["B" & i)
iComp = StrComp(str1, str2, vbBinaryCompare)

Select Case iComp
Case 0
Range("C" & i) = "Match"
Case Else
Range("C" & i) = "Not a match"
End Select
Next i
End Sub

SamT
06-02-2015, 08:22 PM
Option Explicit

Sub SamT
Dim ColumnA as range
Dim Cel As Range
Set ColumnA = Range(Range"A1"), Cells(Rows.COunt, 1).End(xlUp))
For Each Cel In ColumnA
If Cel = Cel.Offset(0, 1) Then ...
With Cel
.Interior Color = vbRed
.Offset(0, 1).Interior.Color = vbRed
End With

End If
Next Cel
End Sub

Hint: In the VBA Editor, press F2, in the search box in the upper left put "Color". Click the binoculars and look around.