concatened strings

[solti]

Hi,

I have a code but can't adjust it for my needs. I just want to have rows highlighted when concatenated values from col A and B doesn't exist in col C and D.

I'f be grateful if you could help me

Sub Compare()
Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range
Dim xValue As String

'Set Range1 = Application.Selection
Set Range1 = Worksheets(1).Range("A1:A11")
Set Range2 = Worksheets(1).Range("C1:C11")

Application.ScreenUpdating = False

Cells.ClearFormats

For Each Rng1 In Range1
    xValue = Rng1.Value & Rng1.Offset(0, 1).Value
      For Each Rng2 In Range2
        If xValue = Rng2.Value & Rng2.Offset(0, 1).Value Then
            If outRng Is Nothing Then
                Set outRng = Rng1
                Else
                Set outRng = Application.Union(outRng, Rng1)
            End If
        End If
    Next
Next

outRng.Interior.ColorIndex = 3
Application.ScreenUpdating = True
End Sub

 

[Marc L]

Hi !

This code was not designed for your need ‼

So the easy way with only formula is
- concatenate columns A & B in a free column
- the same for columns C & D
- use conditional format using MATCH worksheet function
to highlight by comparing concatenations columns …

By code formulas can be used, more efficient than using a double loop !
​

 

[solti]

thanks,
I just thought it will be easier to adapt (as it works pretty quickly) than writting code from the very beginning,

p.

 

[Marc L]

Well, I misread this uggly code like it seems to do the job
(the problem when OP do not share data for testing,
as initial post should be crystal clear and complete !) :
so what's exactly your need via this thread ?!
​

 

[solti]

as you see in the attached file macro colours in red cells which are the same in col A&B vs C&D. I'd like macro to colour these which are different

 

[Marc L]

Could you clearly and technicaly explain :

different on same row ?

What's the problem with your initial code ?
As it highlights equals, for the opposite just change condition in code …
(Edit : in fact initial code is totally illogic !)
Check also range address !

It is not difficult to attach a workbook with a before worksheet
and an after worksheet !
​

 

[solti]

Yes. Diffrrent on the same row means for example a2&b2 <> c2&d2. Than I want the a2 to be red
I thought it's enough to put oposite condition but it's not working

 

[Marc L]

As there is no logic in your code …
So I can't adapt it, what a waste of time !
Just with an easy explanation within initial post …

Neither a concatenation is needed to just compare on the same row !

Efficient way is to use a formula, even within a code !
But that needs you learn Excel basics functions !
With this knowledge, you do not even need a code !

So warming a couple of neurones, like every VBA beginner should do :​

Sub Demo4Noob()
         Dim R&
    With Arkusz2.Cells(1).CurrentRegion
            .Columns(1).ClearFormats
            Application.ScreenUpdating = False
        For R = 1 To .Rows.Count
            If .Cells(R, 1).Value <> .Cells(R, 3).Value Or .Cells(R, 2).Value <> .Cells(R, 4).Value Then _
               .Cells(R, 1).Interior.ColorIndex = 3
        Next
            Application.ScreenUpdating = True
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[solti]

thanks a lot. That exactly what I needed!

 

[solti]

Hi,

I'm wondering how to change the piece of the above code
.Cells(R, 3).Value and .Cells(R, 4).Value
to search not in the given cell but in the whole column (C,D)?

 

[Marc L]

What ? But I asked you several times to well explain at least !​

so what's exactly your need via this thread ?!​

Could you clearly and technicaly explain :

different on same row ?

What's the problem with your initial code ?

It is not difficult to attach a workbook with a before worksheet
and an after worksheet !​

Without any before and after worksheet you just answered this :​

Yes. Diffrrent on the same row means for example a2&b2 <> c2&d2.
Than I want the a2 to be red

So my code respect your answer !

For all the column, just scan all the column updating my code
with a second loop, the same worst way as your code …

Better is to use Find method, see in VBA inner help and its sample.

Or just try to follow the easy logic of post #2 …
As you can also concatenate values in an array variable.
​