I am trying to get a match of the row number with a certain condition in my first sheet Sheet1 and copy the format and paste it in the second sheet Sheet2 where the lookup value matches.
I have data in Sheet1 like this:
| A | B | |
|---|---|---|
| Title | condition | |
| 1 | color1 | A |
| 2 | color2 | B |
| 3 | color4 | C |
| 4 | color2 | D |
| 5 | color1 | A |
| 6 | color2 | D |
And in Sheet2 like this:
like this:
| A | B | C | |
|---|---|---|---|
| Title | condition | ||
| 1 | color1 | A | |
| 2 | color2 | D | |
| 3 | color5 | C | |
| 4 | color2 | B |
I came to the following code for accomplishing this for the first column matches (result in column C):
Sub colorcopy()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range, lookup1 As Range, lookup2 As Range, result As Range
Dim mtch As Variant
Dim i As Long, lr1 As Long, lr2 As Long
Set ws1 = Sheet1
Set ws2 = Sheet2
With ws1
lr1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With ws2
lr2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Set rng1 = ws1.Range("A1:A" & lr1)
Set rng2 = ws1.Range("B1:B" & lr1)
For i = 1 To lr2
Set lookup1 = ws2.Range("A" & i)
Set lookup2 = ws2.Range("B" & i)
Set result = ws2.Range("C" & i)
mtch = Application.Match(lookup1, rng1, 0)
If Not IsError(mtch) Then
ws1.Range("A" & mtch).Copy result
End If
Next i
End Sub
But then I wanted the code to work with multiple conditions (2).
I want to copy the cells of where there’s a match in data from both column A and column B from lookup values in Sheet1 to match in Sheet2.
In formula form this would be accomplished like =MATCH(1,INDEX((Range1=Value1)*(Range2=Value2),),0) for the first row. This seems not possible to be used in Application.Match, but I get stuck here.
Color of sheet1 is like this:
Result of current code is like this:
Wanted result is:
