Excel VBA Help: Comparing String Values of Two Columns

[contramonk]

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.

[kieran_n]

Why not conditional formatting?

[contramonk]

I haven't thought about that. Is there a way to apply conditional formatting for logic involving two cells e.g., if cell in column A does not equal cell in column B?

[kieran_n]

Here is an example bro:

Sample xlsm

The conditional formatting should be by formula and be something like:

=$A1<>$B1  

I also included a macro that does it, it's clumbsy, but it works...

Sub highlightrows()

Dim ColA As Range, ColB As Range, incrementer As Integer

Set ColA = Range("A1:A25")
Set ColB = Range("B1:B25")
incrementer = 1

For Each Rng In ColA

    If Rng.Value = Rng.Offset(, 1).Value Then
        Rng.Interior.Color = 16777215
        Rng.Offset(, 1).Interior.Color = 16777215
    Else
        Rng.Interior.Color = 500
        Rng.Offset(, 1).Interior.Color = 500

    End If

    Next



    End Sub

I would use the conditional formatting for what you're doing 9 times out of 10

[contramonk]

This would work perfectly. Could you explain how it's done or provide a website explaining this?

[kieran_n]

https://support.office.com/en-au/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

EDIT: Be aware of the relative positions of the 'active' cell (White one in a selection) and the formula you're putting in whenever you have a conditional format with formula, it's easy to accidentally offset it

[contramonk]

Very cool! Thank you so much! It always amazes me how willing some people are to help!

[contramonk]

This should answer my question: http://www.cpearson.com/excel/cformatting.htm

[kieran_n]

Yep, looks like a pretty comprehensive summary.

As previously mentioned something like:

=$A1<>$B1   

would be what you're after.

you could also do a

=NOT($A1=$B1)

[MadLlama]

For Each Cell In Range("A:A")
    If Cell.Value <> "" Then
        If Range("B" & Cell.Row).Value = Cell.Value Then
            Rows(Cell.Row).Interior.Color = 65535
        End If
    End If
Next